Results 1 to 2 of 2
2003-06-16, 14:44 #1
- Join Date
- Jun 2003
- Thanked 0 Times in 0 Posts
Custom Menu in Workbook (Excel 2000)
First, this is a great place! I actually get answers!!!
I have a custom menu which I would like to have show up ONLY in a certain workbook. Right now it shows up for me only and for all my excel workbooks. How can I attach it to a workbook, so that others can see it too?
2003-06-16, 15:51 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 342 Times in 335 Posts
Re: Custom Menu in Workbook (Excel 2000)
Here is an example change as appropriate.
This will add a menu button right before the Help button on the main menu it has 3 items and a sub menu which leads to a different button. You will have to add the macros for the buttons. They are now linke to generic macros named "MyMacroA", "MyMacroB" and "MyMacroC"
Add this to the code in thisWorkbook object in VB:
Private Sub Workbook_Open()
Private Sub Workbook_BeforeClose(Cancel As Boolean)
The first adds the menu when the file is opened. The second deletes the menu when the file is closed.
These routines are added into a normal module:
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl
Set cbMainMenuBar = _
Application.CommandBars("Worksheet Menu Bar")
iHelpMenu = _
Set cbcCustomMenu = _
cbcCustomMenu.Caption = "&Custom Menu"
.Caption = "Menu A"
.OnAction = "MyMacroA"
.FaceId = 59
.Caption = "Menu B"
.OnAction = "MyMacroB"
.FaceId = 29
Set cbcCustomMenu = cbcCustomMenu.Controls.Add(Type:=msoControlPopup)
cbcCustomMenu.Caption = "Ne&xt Menu"
.Caption = "EightBall"
.FaceId = 1845
.OnAction = "MyMacroC"
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar"). _
On Error GoTo 0
Hope this helps to get you started,