VBA Tutorial for Custom Toolbars in Excel

Custom toolbars have only buttons you really want.

Visual Basic for Applications, or VBA, makes it possible to program custom behavior in Excel workbooks. One of the many uses of VBA is to build and display custom toolbars with command buttons you select that execute built-in Excel commands as well as custom VBA macros. Instead of using the default Excel menus that are optimized for the average user, you can have absolute control over which commands you have quick access to. Since the code is built in to the workbook itself, your custom menus will be available everywhere you open it.

Instructions

1. Press "Alt" and "F11" at the same time from your Excel worksheet to launch the Visual Basic editor.

2. Double click "ThisWorkbook" in the left navigation pane to open a blank code window.

3. Copy and paste the following code into the blank code window:

   Sub AddMenus()

   Dim cMenu1 As CommandBarControl

   Dim cbMainMenuBar As CommandBar

   Dim iHelpMenu As Integer

   Dim cbcCutomMenu As CommandBarControl

   '(1)Delete any existing one. We must use On Error Resume next _

   in case it does not exist.

   On Error Resume Next

   Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete

   On Error GoTo 0

   '(2)Set a CommandBar variable to Worksheet menu bar

   Set cbMainMenuBar = _

   Application.CommandBars("Worksheet Menu Bar")

   '(3)Return the Index number of the Help menu. We can then use _

   this to place a custom menu before.

   iHelpMenu = _

   cbMainMenuBar.Controls("Help").Index

   '(4)Add a Control to the "Worksheet Menu Bar" before Help.

   'Set a CommandBarControl variable to it

   Set cbcCutomMenu = _

   cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _

   Before:=iHelpMenu)

   '(5)Give the control a caption

   cbcCutomMenu.Caption = "&New Menu"

   '(6)Working with our new Control, add a sub control and _

   give it a Caption and tell it which macro to run (OnAction).

   With cbcCutomMenu.Controls.Add(Type:=msoControlButton)

   .Caption = "Menu 1"

   .OnAction = "MyMacro1"

   End With

   '(6a)Add another sub control give it a Caption _

   and tell it which macro to run (OnAction)

   With cbcCutomMenu.Controls.Add(Type:=msoControlButton)

   .Caption = "Menu 2"

   .OnAction = "MyMacro2"

   End With

   'Repeat step "6a" for each menu item you want to add.

   'Add another menu that will lead off to another menu

   'Set a CommandBarControl variable to it

   Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)

   ' Give the control a caption

   cbcCutomMenu.Caption = "Ne&xt Menu"

   'Add a contol to the sub menu, just created above

   With cbcCutomMenu.Controls.Add(Type:=msoControlButton)

   .Caption = "&Charts"

   .FaceId = 420

   .OnAction = "MyMacro2"

   End With

   End Sub

   Sub DeleteMenu()

   On Error Resume Next

   Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete

   On Error GoTo 0

   End Sub

   Sub MyMacro1()

   MsgBox "I don't do much yet, do I?", vbInformation, "Ozgrid.com"

   End Sub

   Sub MyMacro2()

   MsgBox "I don't do much yet either, do I?", vbInformation, "Ozgrid.com"

   End Sub

   Private Sub Workbook_Activate()

   Run "AddMenus"

   End Sub

4. Press "F5" to save and run the code. This will add the custom menu every time you open the workbook.

Tips & Warnings

• You can also create custom toolbars without VBA through the "Tools > Customize" menu.

References

• Create Custom Menu Items in Excel