Need help with vba to create custom menu.

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875

Hi,
I need help with vba to create custom menu tab next to Addin tab, is this possible? If so please help.

Thanks alot.

The below code is cool enough however it comes under "addin Tab" i want new tab with name "myTab"

Code:
[/FONT]
[FONT=Courier New]Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next 'in case the menu item has already been deleted
    Application.CommandBars("Worksheet Menu Bar").Controls("My Macros").Delete 'delete the menu item
End Sub
Private Sub Workbook_Open()
    Dim cmbBar As CommandBar
    Dim cmbControl As CommandBarControl
    Set cmbBar = Application.CommandBars("Worksheet Menu Bar")
    Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) 'adds a menu item to the Menu Bar
    With cmbControl
        .Caption = "&My Macros" 'names the menu item
        With .Controls.Add(Type:=msoControlButton) 'adds a dropdown button to the menu item
            .Caption = "My Macro No 1" 'adds a description to the menu item
            .OnAction = "RunMyMacro1" 'runs the specified macro
            .FaceId = 1098 'assigns an icon to the dropdown
        End With
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "My Macro No 2"
            .OnAction = "RunMyMacro2"
            .FaceId = 108
        End With
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "My Macro No 3"
            .OnAction = "RunMyMacro3"
            .FaceId = 21
        End With
    End With
End Sub
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Thanks Andrew that is very helpful....


Okay, if we cant create a new tab by VBA can we create new area in Addin Tab next to Menu Commands group in addin tab?

Thanks again...
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Wow...:)!!! i think i'll spend days to understand that..but 'll try again...i got this link the other day i think from Brian but couldnt follow it very well...


If there's a straight code to achieve this things then it'd be great;)....

By Pedie!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,523
Messages
5,529,333
Members
409,863
Latest member
stacy09
Top