How to Create a Menu with VBA

A useful feature of Office Applications like Excel and Word is that you can create your own menu’s and menu items. If you are creating an Add-In you will want to create a menu using VBA.

By doing so you can simply create the menu when your application is started and you can remove the menu again when your application is closed.

The following sub procedure demonstrates how to create a Command Bar and add a few buttons and popups. Both the captions and icons of a button are displayed.

Excel VBA: Different Types Of Excel Macros

---

How to Create a Menu with VBA

---

Notes

In VBA a menu is called a Command Bar. It is good practice to include error trapping to avoid error messages for unexpected events like a application crash.

In Excel 2007 the menu’s have been replaced with the ribbon. However, menu’s are still supported in these versions. Once loaded they are located under the tab ‘Add-Ins’.

Code

'Copy the following code to a new module
'
Sub CreateMyMenu()
    Dim myCB As CommandBar
    Dim myCBtn1 As CommandBarButton
    Dim myCBtn2 As CommandBarButton
    Dim myCPup1 As CommandBarPopup
    Dim myCPup2 As CommandBarPopup
    Dim myCP1Btn1 As CommandBarButton
    Dim myCP1Btn2 As CommandBarButton
 
    ' Delete the CommandBar if it exists already
    On Error Resume Next
    Application.CommandBars("MyMenu").Delete
 
    ' Create a new CommandBar
    Set myCB = CommandBars.Add(Name:="MyMenu", Position:=msoBarFloating)
 
    ' Add button 1 to this bar
    Set myCBtn1 = myCB.Controls.Add(Type:=msoControlButton)
    With myCBtn1
     .Caption = "1st Button"
     .Style = msoButtonCaption   '<- force caption text to show on your button
    End With
 
    ' Add popup menu 1 to this bar - this is a menu that folds out
    Set myCPup1 = myCB.Controls.Add(Type:=msoControlPopup)
    myCPup1.Caption = "Statistics"
 
    ' Add button 1 to popup menu 1
    Set myCP1Btn1 = myCPup1.Controls.Add(Type:=msoControlButton)
    With myCP1Btn1
     .Style = msoButtonAutomatic
     .FaceId = 487
    End With
 
    ' Add button 2 to popup menu 1
    Set myCP1Btn1 = myCPup1.Controls.Add(Type:=msoControlButton)
    With myCP1Btn1
     .Caption = "Click me!"
     .Style = msoButtonIconAndCaption
     .FaceId = 59
     .OnAction = "SubItworks"    '<- call the sub routine SubItWorks
    End With
 
    ' Add a second button to this bar
    Set myCBtn2 = myCB.Controls.Add(Type:=msoControlButton)
    With myCBtn2
     .FaceId = 17  ' <- Face Id 17 is a barchart icon
     .Caption = "Barchart"    '<- shows when hovering mouse over icon
    End With
 
    ' Show the command bar
    myCB.Visible = True
End Sub
 
Private Sub SubItWorks()
    MsgBox ("Eureka, it works!")
End Sub

Excel VBA Tutorial : Workbook Events and Procedures

Usage

Normally you would want to create the menu when the application starts and delete the menu again when the application closes. To make that happen you will need to add another subroutine to the module that will unload the menu.

Sub DeleteMyMenu()
    On Error Resume Next
    CommandBars.FindControl(Tag:="MyMenu").Delete
    CommandBars("MyMenu").Delete
End Sub

On the workbook code page you can add the following code to automatically load the menu at startup and unload it on exit.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    DeleteMyMenu
End Sub
 
Private Sub Workbook_Open()
    CreateMyMenu
End Sub

Relevant URLs

MsoButtonStyle Enumeration for Office 2007 (applies to other versions as well)

---

Courtesy: vba & excel

---

Also See

• Excel Formula Vs Function : What is the difference?
• 7 Simple Tricks to Make Smart and Neat Excel Graphs
• 14 Advanced Excel Tips and Tricks for Basic to Intermediate Users
• How to Reduce Size of Excel Files? – Top 10 Tips