CommandBars

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


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

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext