CommandBars

The commandbar is a generic term that includes menu bars, toolbars and shortcut menus.

CommandBars.GetImageMso

Creating a new CommandBar

You create a new command bar using the Add method from the CommandBars collection:
This is the same in Excel, Word and PowerPoint.

Name - The name of the new command bar. If omitted a default name is assigned to the command bar (such as Custom 1).
Position - The position or type of the new command bar.
MenuBar - The default value is False. True to replace the active menu bar with the new command bar.
Temporary - The default value is False. True to make the new command bar temporary. Temporary command bars are deleted when the container application is closed.

Preventing a Toolbar from being modified

The Protection constants are additive which means you can apply several to the same commandbar.
The following means the toolbar cannot be customised or moved.

Creating a new CommandBar

Dim objCommandBar As CommandBar

Deleting a CommandBar

Disabling a CommandBar

This effectively removes it from view and also from the Customise dialog box list of toolbars

Quick Cleanup

Adding Command Buttons

This code shows you how to add a command button to an existing Menu.

Removing Command Buttons

This code shows you how to remove a command button from an existing Menu.

CommandBarControl Object

Every control that can be added to a commandbar is a CommandBarControl object.
There are three CommandBarControl objects which you can use in your code:
     ● CommandBarButton - This corresponds to the msoControlButton
In Office 2000 a CommandBarComboBox Change event was added.
These are the most common and should be used for assigning to macros.
     ● CommandBarPopup - This corresponds to the msoControlPopup
This represents a menu or a shortcut menu. This type of control has its own Controls collection and allows controls to be added to it.
     ● CommandBarComboBox - This corresponds to the msoControlComboBox
In Office 2000 a CommandBarComboBox Change event was added.

If you are working with msoControlEdit or msoControlDropDown you should use the generic CommandBarControl object.
A dropdown control has a list property and is an array of its list values and a listindex property.

msoControlTypes

Only five of these command bar controls can be added to your custom toolbars and menus.
msoControlType.msoControlButton - Buttons with or without images
msoControlType.msoControlEdit - Textbox allows you to enter text
msoControlType.msoControlDropDown - Only allows you to select from the list.
msoControlType.msoControlComboBox - Lets you enter a value as well as being able to select from the list.
msoControlType.msoControlPopup - Used for menus and submenus.

CommandBarControl Properties

These properties and methods are shared by all the commadbarcontrols

BeginGroup	True if a separator appears before the control.
Built-in	True if the control is built-in
Caption	The text that is displayed for the control, If the control shows only an image, the caption appears when you move the mouse over the control. This can include an a&mpersand to indicate the hot key.
Enabled	True if the control can be clicked.
FaceId	A number representing a graphic image displayed next to the control's caption.
OnAction	The name of the VBA macro to be executed when the control is clicked. This is not applicable to msoControlPopup
Parameter
ShortcutText	Menu bar commands ??
State	Used for checkboxes ?? msoButtonDown
Style	msoButtonStyle - Determines whether the button appears with a caption and/or image.
Tag
ToolTipText	The text that appears when the user moves the mouse pointer over the control.
Type	An integer that determines the type of the control. This cannot be changed once the command has been added.

Parameter

This property can be used to hold any text string that might be useful.
One use might be to use the same Macro for several commands and then examine the Parameter property to determine which button was pressed.
This method is similar to the Application.Caller used in user defined functions.

This can be very useful for toggling

Tag

This property can be used to hold any text string that might be useful and can be used in addition to the Parameter property.

Adding Commands

when adding a built-in control to a command bar, you pass the ID property for the built-in control in the ID argument of the Controls collection's Add method.

Type - The type of control to be added to the specified command bar. Can be one of the following MsoControlType constants.
Id - An integer that specifies a built-in control. If the value of this argument is 1, or if this argument is omitted, a blank custom control of the specified type will be added to the command bar.
Parameter - For built-in controls, this argument is used by the container application to run the command. For custom controls, you can use this argument to send information to Visual Basic procedures, or you can use it to store information about the control (similar to a second Tag property value).
Before - A number that indicates the position of the new control on the command bar. The new control will be inserted before the control at this position. If this argument is omitted, the control is added at the end of the specified command bar.
Temporary - The default value is False. True to make the new control temporary. Temporary controls are automatically deleted when the container application is closed.

Adding Custom Commands

when you create a custom command bar button, you must set both the Style and Caption properties to make sure that a caption will appear on the button.

Assigning Shortcut Keys

The following adds a menu that features a shortcut key.
You must use the MacroOptions to actually create the shortcut key (?)

CommandBars.FindControl

You can also use the FindControl method to return a CommandBarControl object.

This is only available on the CommandBars object.

If no controls are found that fit the criteria then "Nothing" is returned.
If this method find two or more controls that fit the search criteria, the first control that is found is returned.

CommandBars.FindControl(Type, Id, Tag, Visible, Recursive)

Type		can be any of the above types
Id		The identifier of the control. This identifies the action performed by the control.
Tag		The tag value of the control to be searched for
Visible	False	True to indicate that only visible controls are to be searched.
Recursive	False	True to indicate the commandbar and all of its pop ups sub toolbars are to be included in the search.

The Recursive argument is only available when used on the CommandBar object.

Finding Built-in Commands

Every built-in command (inc menus) has a unique Id associated with it.
This is the best property to use when searching for built-in commands.

Finding Custom Commands

This method can be used to quickly locate controls on toolbars and menus.
This can be really useful when working with your custom toolbars and menus because you can assign a Tag property to all of these commands.
Assuming that all your commands have a unique Tag assigned to them this method will return the corresponding CommandBarControl is returned

Recursive Argument

This argument specifies if you want to recursively search the whole command bar including any drop-down menus and extension controls.
There are no controls passed the third level.
The default argument is False.

Using the Tag property

Moving Commands

moves the control to end