As we have said, one way in which menu bars and toolbars differ from menus and submenus is in their creation. To create a new menu bar or shortcut menu, we use the Add method of the CommandBars collection. The syntax for the Add method is:
CommandBarsObject.Add(Name, Position, MenuBar, Temporary)
The optional Name parameter is the name of the new command bar. If this argument is omitted, Excel VBA assigns a default name (such as "Custom 1") to the command bar. The optional Position parameter gives the position of the new command bar. This can be set to msoBarLeft, msoBarTop, msoBarRight, msoBarBottom , msoBarFloating (for a floating command bar), or msoBarPopup (for a shortcut menu).
The optional Boolean MenuBar parameter is set to True for a menu bar and False for a toolbar. The default value is False, so if the argument is omitted, a toolbar is created. Note that if you create a new menu bar and make it visible, it will replace the existing Excel menu bar! If this happens, you can still exit Excel by typing Alt-F4, and the normal Excel menu will reappear the next time that you launch Excel.
Setting the optional Temporary parameter to True makes the new command bar temporary. Temporary command bars are deleted when Excel is closed. The default value is False.
To illustrate, the following code creates a new floating toolbar called "Custom Toolbar" and makes it visible:
Dim cbar As Office.CommandBar
Set cbar = Application.CommandBars.Add("Custom Toolbar", msoBarFloating, False, True) cbar.Visible = True
It is important to note that, if a CommandBar object by the name Custom Toolbar already exists, the previous code will produce a runtime "Invalid procedure call" error. Thus, we really should test for the existence of the CommandBar object before using the Add method, as shown in Example 12-2.
Was this article helpful?