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

 

[lnagel]

Addins tab does not appear in menus either - even tho it is checked

 

[lnagel]

all other menus work (developer, etc) if checked

 

[RoryA]

Remove the red line here:

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

then run the code. What happens?

 

[lnagel]

The code runs without the "On Error" line - I added that line earlier when writing as the code was getting hung up on the Workbook Close (saying it couldn't find the commandbar to delete) So that problem appears to be gone now but still no menu.... Im googling the issue and apparently there are others out there running Excel 2016 with same problem... What is your version of Excel?

 

[RoryA]

2010 currently. I'll test 2016 when I get home.

In case you aren't aware, this is not the recommended way of customising the ribbon.

 

[lnagel]

I just sent the spreadsheet to myself at work - I think I have 2013 there and will test when i get there.... Totally open to suggestions for alternative ways to accomplish this- Thanks for your help

 

[RoryA]

This is how it should be done: http://www.rondebruin.nl/win/section2.htm

 

[Logit]

Just to be certain ... are the macros located in the ThisWorkbook module ?

 

[Logit]

http://www.vbaexpress.com/kb/getarticle.php?kb_id=427

 

[lnagel]

Yes Macros located in "This Workbook" Module... So here's a weird thing I sent to work and when I tried it - it works there! Other weird thing - also have Excel 2016 at work. Only difference being i have 64 Bit at work and 32 bit at home. So now not sure what to think. I will look at your links and try a different approach if possible. I really appreciate your time in assisting on this.