Trying to Add New Menu Item to Menu Bar

[lnagel]

Below is the code I currently have. I don't get any errors but nothing happens (that I can see anyway)

Pretty sure the code is running because I was getting an error in beforeclose until I add the On Error Resume Next

Running Excel 2016

Thanks in advance

Private Sub Workbook_Open()
Dim mymenubar As CommandBar
Dim newmenu As CommandBarPopup
Dim ctrl1, ctrl2 As CommandBarButton
On Error Resume Next
   
Set mymenubar = Application.CommandBars("Worksheet menu Bar")
Set newmenu = mymenubar.Controls.Add(Type:=msoControlPopup, Temporary:=True, before:=2)
newmenu.Caption = "SetBilder"
Set ctrl1 = newmenu.Controls.Add(Type:=msoControlButton, ID:=1)
    With ctrl1
        .Caption = "Print All"
        .Style = msoButtonCaption
        .OnAction = "'" & ThisWorkbook.Name & "'!printall"
    End With
        
Set ctrl2 = newmenu.Controls.Add(Type:=msoControlButton, ID:=1)
    With ctrl2
        .Caption = "Print No Cost"
        .Style = msoButtonCaption
        .OnAction = "'" & ThisWorkbook.Name & "'!printnocost"
    End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim CmdBar As CommandBar
Dim CmdBarMenu As CommandBarControl
Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
Set CmdBarMenu = CmdBar.Controls("SetBilder")
CmdBarMenu.Delete
End Sub

 

[Domenic]

If you open your workbook and make sure that macros are enabled, it should add a new tab called "Add-Ins", where you should see your new menu. If not, try removing "On Error Resume Next" from the workbook open event handler, save the workbook, close, and re-open it. Do you now get error? if so, which one? And, on which line?

 

[Logit]

.
This version of your macro runs without error here :

Option Explicit


Private Sub Workbook_Open()
Dim mymenubar As CommandBar
Dim newmenu As CommandBarPopup
Dim ctrl1, ctrl2 As CommandBarButton
On Error Resume Next
   
Set mymenubar = Application.CommandBars("Worksheet menu Bar")
Set newmenu = mymenubar.Controls.Add(Type:=msoControlPopup, Temporary:=True, before:=2)
newmenu.Caption = "SetBuilder"
Set ctrl1 = newmenu.Controls.Add(Type:=msoControlButton, ID:=1)
    With ctrl1
        .Caption = "Print All"
        .Style = msoButtonCaption
        .OnAction = "'" & ThisWorkbook.Name & "'!printall"
    End With
        
Set ctrl2 = newmenu.Controls.Add(Type:=msoControlButton, ID:=1)
    With ctrl2
        .Caption = "Print No Cost"
        .Style = msoButtonCaption
        .OnAction = "'" & ThisWorkbook.Name & "'!printnocost"
    End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim CmdBar As CommandBar
Dim CmdBarMenu As CommandBarControl
Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
Set CmdBarMenu = CmdBar.Controls("SetBuilder")
CmdBarMenu.Delete
End Sub

 

[lnagel]

Without the On Error Resume I get the error in the Workbook Before Close on CmdBarMenu.Delete command so I know it is running. If I uses the addins menu to enable the Menu will it not make the menu active for all Excel Workbooks? - I need it to be open only for this one

 

[lnagel]

This ran in your Excel without doing anything except copy and Paste?????

 

[Logit]

.

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 33333]This ran in your Excel without doing anything except copy and Paste?????[/COLOR]

No ... it ran after I removed the "On Error Resume Next", then corrected one or two small corrections. I don't recall what they were now. Guess I could go back and re-run your original
macro again to see.

However, as Domenic indicated, make certain you have MACROS ENABLED in the EXCEL SETTINGS.

You should be able to simply copy the code I posted in #3 and make it run.

 

[lnagel]

hmmm...The Macros themselves are running as I can see them when I click "Macros" in Developer Tab and they do function if I select and run. The Part that is not happening is the Menu entitled "SetBilder" does not appear in the Tabs (Menus)

 

[RoryA]

Your code works fine for me, as posted. It adds the menu item to the AddIns tab, as Domenic mentioned. There is no other option for that using VBA.

 

[lnagel]

Yes I have the "Addins" Tab Checked - Still no Menu in Menu Bar?? I'm struggling here as to why this doesnt work??

 

[RoryA]

If you can see the Addins tab, what is on it?