Trying to Add New Menu Item to Menu Bar

lnagel

Board Regular
Joined
May 28, 2018
Messages
117
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
Code:
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
 
Last edited by a moderator:

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Remove the red line here:

Rich (BB code):
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Just to be certain ... are the macros located in the ThisWorkbook module ?
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top