Bespoke Microsoft Office Development 
 Consultancy|Excel|Word|PowerPoint|Outlook|

VBA

|Tools|Newsletter 
 VBA > Toolbars & Menus > CommandBars< Previous | Next > 

 

What is a CommandBar ?

 
 

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.

 
 
1
2
3
4
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

 
 
5
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.

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

 

Creating a new commandbar

 
 

Dim objCommandBar As CommandBar

 
 
7
8
Set objCommandBar = Application.CommandBars.Add(

   


 

Deleting a CommandBar

 
 
9
objCommandBar.Delete
   


 

DIsablying a CommandBar

 
 

CommandBars("MyToolbar").Enabled = False

 
 

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

 
 

Is there a quick way to disable the controls on a toolbar ??

 

 

Quick Cleanup

 
 
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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
   

 
30
CommandBars.Reset ??
   



 © Better Solutions Limited 17-Jul-2014< Previous | Top | Next >