Microsoft Office Tutorials and References
In Depth Information
Example 12-1. Listing Excel's CommandBar Objects
The code in Example 12-1 will print a list of all of the CommandBar objects to the immediate
window. You may be surprised at the large number of objects, most of which are not currently
Example 12-1. Listing Excel's CommandBar Objects
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
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.
Search JabSto ::

Custom Search