Note
The use of CommandBars in some Microsoft Office applications has been superseded by the new Ribbon user interface. For more information, search help for the keyword "Ribbon."

Some container applications don't provide a way for you to create new menu bars, so you'll need to create menu bars by using Visual Basic. After you've created a menu bar in Visual Basic, you can customize it by using the container application's interface or by using Visual Basic.

Adding menu bars at run time

When you add a menu bar to an application at run time, you use the Add method for the CommandBars collection and specify True for the MenuBar argument. The following example adds a menu bar that cannot be moved. The example also docks this menu bar along the right side of the application window. 

Set menubar = CommandBars.Add _ (Name:="mBar", Position:=msoBarRight, MenuBar:=True) With menubar .Protection = msoBarNoMove .Visible = True End With

Making run-time modifications to menu bars

You can make modifications to both the menu bar and the controls on that menu bar at run time. The changes you make to the menu bar may affect its appearance or its position; changes you make to the controls depend on the control type. The properties and methods listed in the following table are the most common ones used to modify menu bars at run time.

Property or method Description
Add Adds a menu bar by using the Add method of the CommandBars collection and specifying True for the MenuBar argument.
Enabled If this property is set to True, the user can make the specified menu bar visible by using Visual Basic code. If this property is set to False, the user cannot make the menu bar visible, but it will appear in the list of available command bars.
Protection Makes it possible for you to protect the menu bar from specific user actions. Can be one of, or a combination of, the following MsoBarProtection constants: msoBarNoChangeDock, msoBarNoChangeVisible, msoBarNoCustomize, msoBarNoHorizontalDock, msoBarNoMove, msoBarNoProtection, msoBarNoResize, and msoBarNoVerticalDock.
Position Specifies the position of the new menu bar relative to the application window. Can be one of the following MsoBarPosition constants: msoBarLeft, msoBarTop, msoBarRight, msoBarBottom, msoBarFloating, msoBarPopup (used to create shortcut menus), or msoBarMenuBar (Macintosh only).
Visible Specifies whether the control will be displayed or hidden from the user. If the control is hidden from the user, the menu bar name will still appear in the list of available command bars.

The following example hides the active menu bar and replaces it with a temporary menu bar that's docked along the right side of the application window and protected from the user. 

Set oldMbar = CommandBars.ActiveMenuBar Set newMbar = CommandBars.Add _ (Name:="newMenubar", Position:=msoBarRight, _ MenuBar:=True, temporary:=True) With newMbar .Visible = True .Protection = msoBarNoMove End With

Merging menu bars at run time

If you have custom menu bars in an application that's intended to be an add-in, you may want to specify how the controls will be represented in the container application. You can use the OLEMenuGroup property of the CommandBarPopup object to specify how the menu bar merging will occur.

Making run-time modifications to menu items

The range of modifications you can make to a menu item depends on the control type. Generally, buttons are either enabled or hidden. Edit boxes, drop-down list boxes, and combo boxes are more versatile in that you can add or delete items from the list, and you can determine the action performed by looking at the value selected. You can change the action of any control to either a built-in or custom function.

The following table lists the most common properties and methods for changing the state, action, or contents of a control.

Property or method Purpose
Add Adds a menu item to a command bar. Can be one of the following MsoControlType constants for the Type argument for a built-in control: msoControlButton, msoControlEdit, msoControlDropdown, or msoControlComboBox.
AddItem Adds an item to the drop-down list portion of a drop-down list box or combo box. You can specify the index number of the new item in the existing list, but if this number is larger than the number of items in the list, the AddItem method fails.
Style Specifies whether (and if so, how) the button face displays its icon or its caption, or both. Can be one of the following MsoButtonStyle constants: msoButtonAutomatic, msoButtonIcon, msoButtonCaption, msoButtonIconAndCaption, msoButtonIconAndCaptionBelow, msoButtonIconAndWrapCaption, msoButtonIconAndWrapCaptionBelow, or msoButtonWrapCaption.
OnAction Specifies the procedure to be run whenever the user changes the value of the specified control.
Visible Specifies whether the control will be displayed or hidden from the user.

This following example adds a temporary pop-up control named "Custom" at the end of the active menu bar, and then it adds a button control named "Import" to the Custom pop-up command bar. 

Set myMenuBar = CommandBars.ActiveMenuBar Set newMenu = myMenuBar.Controls.Add(Type:=msoControlPopup, _ Temporary:=True) newMenu.Caption = "Custom" Set ctrl1 = newMenu.Controls _ .Add(Type:=msoControlButton, Id:=1) ctrl1.Caption = "Import" ctrl1.TooltipText = "Import" ctrl1.Style = msoButtonCaption
Adding and Managing Menu Bars and Menu Items ofv

See also: