Custom Menus

[spaz9876]

I created a custom menu on the main toolbar with a custom item within the menu. When someone else opens the workbook on their desktop, it doesnt show the custom menu item. how can I get it to stay with the workbook?

[Norie]

One way would be to create/delete this menu using code in the Workbook's Open/Close events.

The best way to start with that would be to turn on the macro recorder (Tools>Macro...Record a New Macro...).

Then manually create the menu.

This should generate code that you can use when the workbook opens.

[spaz9876]

Ok I tried that but when I run the macro, it ends up just recreating another menu bar and doesnt include the macro assigned to the button. Also, I dont know how to set the macro to autorun when the workbook first opens.

[Bob Phillips]

Ok I tried that but when I run the macro, it ends up just recreating another menu bar and doesnt include the macro assigned to the button. Also, I dont know how to set the macro to autorun when the workbook first opens.

If you put code in the appropriate workbook open event, and delete it in the
close it will exist only for that workbook.
Here is an example of a building a commandbar on the fly when you open a workbook. It adds a sub-menu to the Tools menu.

Private Sub Workbook_Open() 
Dim oCb As CommandBar 
Dim oCtl As CommandBarPopup 
Dim oCtlBtn As CommandBarButton 
Set oCb = Application.CommandBars("Works*heet Menu Bar") 
With oCb 
    Set oCtl = .Controls("Tools").Controls.Ad*d(Type:=msoControlPopup, temporary:=True) 
    oCtl.Caption = "myButton" 
    With oCtl 
        Set oCtlBtn = .Controls.Add(Type:=msoControl*Button, temporary:=True) 
        oCtlBtn.Caption = "myMacroButton" 
        oCtlBtn.FaceId = 161 
        oCtlBtn.OnAction = "myMacro" 
    End With 
End With 
End Sub 
 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
Dim oCb As CommandBar 
Set oCb = Application.CommandBars("Works*heet Menu Bar") 
oCb.Controls("Tools").Controls*("myButton").Delete 
End Sub

To add this, go to the VB IDE (ALT-F11 from Excel), and in the explorer pane, select your workbook. Then select the 'ThisWorkbook' object (it's in Microsoft Excel Objects which might need expanding). Double-click the 'ThisWorkbook' and a code window will open up. Copy this code into there, changing the caption and action to suit.



This is part of the workbook, and will only exist with the workbook, but will be available to anyone who opens the workbook.

[Norie]

xld

Why not replace this

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
	Dim oCb As CommandBar 
	Set oCb = Application.CommandBars("Works­heet Menu Bar") 
	oCb.Controls("Tools").Controls­("myButton").Delete 
End Sub

with this

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
	Application.CommandBars("Works­heet Menu Bar").Reset 
End Sub

[Bob Phillips]

xld

Why not replace this

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
	Dim oCb As CommandBar 
	Set oCb = Application.CommandBars("Works­heet Menu Bar") 
	oCb.Controls("Tools").Controls­("myButton").Delete 
End Sub

with this

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
	Application.CommandBars("Works­heet Menu Bar").Reset 
End Sub

Because that would remove any other customisations which is not smart,

[spaz9876]

Ok I copied and pasted and get Run-time error '5': Invalid procedure call or arguement.

Private Sub Workbook_Open()
    Dim oCb As CommandBar
    Dim oCtl As CommandBarPopup
    Dim oCtlBtn As CommandBarButton
Set oCb = Application.CommandBars("Worksheet Menu Bar")
    With oCb
    Set oCtl = .Controls("Tools").Controls.Add(Type:=msoControlPopup, temporary:=True)
    oCtl.Caption = "Price List Menu"
    With oCtl
        Set oCtlBtn = .Controls.Add(Type:=msoControl*Button, temporary:=True)
        oCtlBtn.Caption = "Export Quote"
        oCtlBtn.FaceId = 161
        oCtlBtn.OnAction = "CopySheetAndDeleteRows()"
    End With
    End With
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim oCb As CommandBar
    Set oCb = Application.CommandBars("Worksheet Menu Bar")
    oCb.Controls("Tools").Controls("Price List Menu").Delete
End Sub

Did I put in the macro wrong? "CopySheetAndDeleteRows ()"

[Bob Phillips]

Ok I copied and pasted and get Run-time error '5': Invalid procedure call or arguement.

Did I put in the macro wrong? "CopySheetAndDeleteRows ()"

Are you sre that you put it in the ThisWorkbook module as I described.

There is also one other thing to beware of, due to the beta Google

There is a nasty hidden - in one line. The line

Set oCtlBtn = .Controls.Add(Type:=msoControlButton, temporary:=True)

looks ok, but if and when you get the problem, remove the - between Control and Button in msoControlButton.

[spaz9876]

OOPS - Ok that worked.
Any way to change it to its own menu instead of under "tools"?

[Bob Phillips]

OOPS - Ok that worked.
Any way to change it to its own menu instead of under "tools"?

Same place as before

Private Sub Workbook_Open()
    Dim oCtl As CommandBarPopup
    Dim oCtlBtn As CommandBarButton
Set oCtl = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup, temporary:=True)
    oCtl.Caption = "Price List Menu"
    With oCtl
    Set oCtlBtn = .Controls.Add(Type:=msoControlButton, temporary:=True)
    oCtlBtn.Caption = "Export Quote"
    oCtlBtn.FaceId = 161
    oCtlBtn.OnAction = "CopySheetAndDeleteRows()"
    End With
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim oCb As CommandBar
    Set oCb = Application.CommandBars("Worksheet Menu Bar")
    oCb.Controls("Price List Menu").Delete
End Sub

[lucas]

Take a look at J-Walks solution. I think you will like it.
http://j-walk.com/ss/excel/tips/tip53.htm

its all I use for menu's

[spaz9876]

Thank you XLD and Lucas. You've been a big help.