Monday, 15 December 2008

Create custom menu in Excel VBA

Public Sub CreateMenu()

    Dim MenuObject As CommandBarPopup
    Dim MenuItem As Object
    Dim SubMenuItem As Object

    On Error Resume Next
    DeleteMenu

    Set MenuObject = Application.CommandBars(1). _
        Controls.Add(Type:=msoControlPopup, _
        Before:=10, _
        Temporary:=True)
    MenuObject.Caption = "&SampleMenu"

' Add menu Item with Icon (FaceId=424)    
    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
    MenuItem.OnAction = "RefreshOnly"
    MenuItem.Caption = "&Menu Item 1"
    MenuItem.FaceId = 424
        
' Add another Menu Item after divider
    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
    MenuItem.OnAction = "RefreshBondsVsRepos"
    MenuItem.Caption = "M&enu Item 2"
    MenuItem.FaceId = 425
    MenuItem.BeginGroup = True

End Sub

Public Sub DeleteMenu()

    On Error Resume Next
    Application.CommandBars(1).Controls("SampleMenu").Delete

End Sub
To get a list of FaceId icons see this

No comments:

Post a Comment