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. The new menu bar becomes active whenever the user presses the ALT key.
Set menubar = CommandBars.Add _
(Name:="mBar", Position:=msoBarRight, MenuBar:=True)
With menubar
.Protection = msoBarNoMove
.Visible = True
End WithMaking run-time modifications to menu bars
You can make modifications to the 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, 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 | Make 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 is 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 WithMerging 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.
If either the container application or the server doesn't implement command bars, "non-Office" merging will occur: the menu bar will be merged, along with all the toolbars from the server, and none of the toolbars from the container application will be merged. You can also use the OLEUsage property to specify how menu bar merging will occur.
If both the container application and the server implement command bars, "Office-in-Office" merging will occur: the command bar controls are embedded in the Office application, control by control. Both of these properties (OLEMenuGroup and OLEUsage) are relevant only for pop-up controls on menu bars, because menus are considered on the basis of their menu group category.
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 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