PDA

View Full Version : [SOLVED] Custom Menus



spaz9876
06-29-2005, 11:32 AM
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
06-29-2005, 12:23 PM
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
06-29-2005, 12:51 PM
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
06-29-2005, 01:00 PM
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
06-29-2005, 01:03 PM
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
06-29-2005, 01:06 PM
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
06-29-2005, 01:14 PM
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
06-29-2005, 01:24 PM
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 :devil:

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
06-29-2005, 01:29 PM
OOPS - Ok that worked.
Any way to change it to its own menu instead of under "tools"?

Bob Phillips
06-29-2005, 01:50 PM
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
06-29-2005, 04:52 PM
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
06-30-2005, 06:02 AM
Thank you XLD and Lucas. You've been a big help.