Creating a New Menu Bar or Toolbar

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?

+1 0
Black Jack Tactics Conquering The Game In Casinos

Black Jack Tactics Conquering The Game In Casinos

So, you want to learn how to play Blackjack. Youre in good hands. There is no better ship to chart those learning waters than the one youre riding on right now.

Get My Free Ebook


    How to create a menu bar on excell using vba?
    1 year ago
    How To Create a Toolbar Using VBA?
    6 months ago
  • adiam
    How to create commandbar in vba exile 2019?
    4 months ago
  • Feorie
    How to create a new toolbar in excel?
    4 months ago
    How to create menu on desktop with vba excel 2016?
    3 months ago
  • alexander
    How to add lightbulb bar to vba excel?
    3 months ago
  • John
    How to delete new bar in menu bar in excel?
    2 months ago

Post a comment