Creating a Menu Bar : Menu « Application « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Application
3. Menu

Creating a Menu Bar

 
Public Sub AddMenuItemExample() 
    Dim cbWSMenuBar As CommandBar 
    Dim cbc As CommandBarControl 

    Set cbWSMenuBar = Application.CommandBars("Worksheet Menu Bar") 
    Set cbc = cbWSMenuBar.Controls.Add(Type:=msoControlPopup, Temporary:=True) 
    cbc.Tag = "MyMenu" 
    With cbc 
        .Caption = "&My Menu" 
        With .Controls.Add(Type:=msoControlButton, Temporary:=True) 
            .Caption = "Item &1" 
            .OnAction = "ThisWorkbook.SayHello" 
            .Tag = "Item1" 
        End With 
        With .Controls.Add(Type:=msoControlButton, Temporary:=True) 
            .Caption = "Item &2" 
            .OnAction = "ThisWorkbook.SayHello" 
            .Tag = "Item2" 
        End With 
        With .Controls.Add(Type:=msoControlButton, Temporary:=True) 
            .Caption = "Item &3" 
            .OnAction = "ThisWorkbook.SayHello" 
            .Tag = "Item 3" 
        End With 
        With .Controls.Add(Type:=msoControlButton, Temporary:=True) 
            .Caption = "Item &4" 
            .OnAction = "ThisWorkbook.SayHello" 
            .BeginGroup = True 
            .Tag = "Item4" 
        End With 
        With .Controls.Add(Type:=msoControlButton, Temporary:=True) 
            .Caption = "Item &5" 
            .OnAction = "ThisWorkbook.SayHello" 
            .Tag = "Item5" 
            .BeginGroup = True 
        End With 
        With .Controls.Add(Type:=msoControlButton, Temporary:=True) 
            .Caption = "Item &6" 
            .OnAction = "ThisWorkbook.SayHello" 
            .Tag = "Item6" 
        End With 
    End With 
End Sub 
Private Sub SayHello() 
    MsgBox "Hello", vbOKOnly 
End Sub 

 

Related examples in the same category