Public Sub ShowCmdBars()
Dim sType as string, cbar as CommandBar
For Each cbar In Application.CommandBars Select Case cbar.Type
Case msoBarTypeNormal ' A toolbar sType = "Normal" Case msoBarTypeMenuBar ' A menu bar sType = "Menu bar" Case msoBarTypePopup ' Menu, submenu sType = "Popup" End Select
Debug.Print cbar.Name & "," & sType & "," & cbar.Visible Next End Sub
If you execute this code, you should get the following entries, among many others:
Worksheet Menu Bar,Menu bar,True Chart Menu Bar,Menu bar,False
This indicates that Excel's main menu bars are different for worksheets than for chartsheets, as is evident if you look at the menus themselves. The worksheet menu bar has different controls than the Chart menu bar. Thus, if you want to add a custom menu item to Excel's "main" menu bar, regardless of what type of sheet is currently active, you will need to do so for both the Worksheet Menu Bar and the Chart Menu Bar.
There is a slight complication concerning the CommandBars property that we should discuss. When qualified with the Application object, as in Application.CommandBars, this property returns the collection of all available built-in and custom command bars for the application which in this case is Excel. This is why we used the fully qualified expression
Application.CommandBars in Example 12-1. Note that from a standard code module, we can skip the qualification and just write CommandBars.
However, from a Workbook, the CommandBars property returns a different collection. In particular, there are two possibilities. When the workbook is embedded within another application and Excel is activated by double-clicking on that embedded workbook, the CommandBars collection returns the collection of command bars that are available in that setting. This may be different from the full collection of Excel command bars. If the workbook is not embedded in another application, then the CommandBars property returns Nothing.
Note also that the Workbook object has a CommandBars property. However, this property is meaningful only when the workbook is embedded within another application, in which case the property returns the CommandBars collection for that application. When applied to a nonembedded workbook, the property returns Nothing. Moreover, there is no programmatic way to return the set of command bars attached to a workbook.
Was this article helpful?