CommandBars

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

CommandBars.GetImageMso

imgImageControl.Picture = Application.Commandbars.GetImageMso("id", 16, 16) 

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.

Application.CommandBars.Add Name:="MyCustomToolbar" 
                            Position:=msoBarPosition.msoBarFloating 
                            MenuBar:=False 
                            Temporary:=True or msoTriState.msoTrue 

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

Application.CommandBars("MyCustomToolbar").Protection = msoBarProtection.msoBarNoCustomize 

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.

Application.CommandBars("MyCustomToolbar").Protection = msoBarNoCustomize + msoBarNoMove 

Creating a new CommandBar

Dim objCommandBar As CommandBar

Set objCommandBar = Application.CommandBars.Add( 

Deleting a CommandBar

objCommandBar.Delete 

Disabling a CommandBar

CommandBars("MyToolbar").Enabled = False 

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

Quick Cleanup

Private Sub RemoveAllCommands 
Dim objCommandBarControl As CommandBarControl 

'ignore any errors while cleaning up - err NO !!!!
On Error Resume Next 

With CommandBars 
'find a commandbarbutton with our tag
  Set objCommandBarControl = .FindControl(Tag:="MyTag") 

   Do Until objCommandBarControl Is Nothing 
'delete the control we found
      objCommandBarControl.Delete 

'find the next one
     Set objCommandBarControl = .FindControl(Tag:="MyTag") 
   Loop 
End With 

End Sub 

CommandBars.Reset ?? 

Adding Command Buttons

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

Public Sub BET_AddCommandToTools() 
Dim objCommand As CommandBarPopup 
Dim objButton As CommandBarButton 

   Set objCommand = Application.CommandBars("Worksheet Menu Bar").Controls("Tools") 
   Set objButton = objCommand.Controls("New Command") ???? 
   Set objButton = objCommand.Controls.Add(Type:=msoControlButton, Before:=3) 

   objButton.Caption = "New Command" 
   objButton.OnAction = "YourMacroName" 
End Sub 

Public Sub YourMacroName() 
   Your code 
End Sub 

Removing Command Buttons

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

Public Sub BET_RemoveCommandFromTools() 
Dim objCommand As CommandBarPopup 

   Set objCommand = Application.CommandBars("Worksheet Menu Bar").Controls("Tools") 
   objCommand.Controls("Tools").Delete 
End Sub 

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

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.

Private Sub CalledBySeveral() 
   Select Case CommandBars.ActionCommand.Parameter 
      Case "Ascending": 
      Case "Descending": 
   End Select 
End Sub 

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.

CommandBars("MyToolbar").Controls.Add Type:=msoControlType.msoControlButton, _ 
                                      Id= 
                                      Parameter= 
                                      Before= 
                                      Temporary= 

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 (?)

Set objtoolmenu = CommandBars(1).FindControl(Id:=30007) 
Set objnewmenu = objToolsMenu.Controls.Add(Type:=msoControlButton) 
With objnewmenu 
   .OnAction = "MyCustomMacro" 
   .ShortcutText = "Ctrl + Shift + C" 
End With 

Application.MacroOptions Macro:="MyCustomMacro", _ 
                         HasShortcutKey:=True, _ 
                         ShortcutKey:="C" 

Application.OnKey "^+c", "MyCustomMacro"  ?? 

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)

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

Set objStandardControl = CommandBars("Standard").FindControl(Id:=100, Recursive:=True) 
Set objCommandBar = CommandBars("Standard").FindControl(Id:=100, Recursive:=True).CommandBar 

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.

Public Sub SearchForID() 
Dim objcommandbar As CommandBar 
Dim objcontrol As CommandBarControl 
   For Each objcommandbar In CommandBars 
   Set objcontrol = objcommandbar.FindControl(ID:=283, Recursive:=True) 
   If Not (objcontrol Is Nothing) Then 
      Debug.Print objcommandbar.Name 
   End If 
   Next objcommandbar 
End Sub 

Using the Tag property

Dim objCommandBar As Office.CommandBar 
Dim objControl As Office.CommandBarControl 

Set objCommandBar = Application.CommandBars("Formatting") 
Set objControl = objCommandBar.FindControl(Tag:="UniqueTag") 

Moving Commands

Dim objControl As CommandBarControl 
Dim objControl2 As CommandBarControl 
Dim objControl3 As CommandBarControl 
Set objControl = Application.CommandBars("Cell").FindControl(ID:=3125) 

moves the control to end

Set objControl2 = objControl.Move(CommandBars("Cell")) 
Set objControl3 = objControl2.Move(CommandBars("Cell"),7)