Custom Menu (not tool bar)

[TJM]

I would like to embed a custom menu (not a tool bar) that will be available
to all users who open the spreadsheet. The primary purpose of the menu would
be to simply navigate to different tabs within the spreadsheet and possibly
to run simple macros. Also, given my lack of VB programming skills, it would
be preferable if the code was simple enough for me to copy and paste as I add
tabs to the worksheet. For example, if you are able to create a worksheet
embedded menu that navigates to cell “A1†on a tab called “sheet 1†I would
hope the code would be such that I could copy it and replace “sheet 1†with
another tab name. Finally, I am aware of how to add custom menu items (not
tool bars) using the excel wizard, but those menus are not available to all
users who use the spreadsheet. Your help with this would be greatly
appreciated.

 

[ruic]

Sample Code to Add/Delete Custom Menu Items
You can simply copy the code below into any standard Excel Module

Sub AddMenus()

Dim cMenu1 As CommandBarControl

Dim cbMainMenuBar As CommandBar

Dim iHelpMenu As Integer

Dim cbcCutomMenu As CommandBarControl



'(1)Delete any existing one. We must use On Error Resume next _

in case it does not exist.

On Error Resume Next

Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete

On Error GoTo 0



'(2)Set a CommandBar variable to Worksheet menu bar

Set cbMainMenuBar = _

Application.CommandBars("Worksheet Menu Bar")



'(3)Return the Index number of the Help menu. We can then use _

this to place a custom menu before.

iHelpMenu = _

cbMainMenuBar.Controls("Help").Index



'(4)Add a Control to the "Worksheet Menu Bar" before Help.

'Set a CommandBarControl variable to it

Set cbcCutomMenu = _

cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _

Before:=iHelpMenu)



'(5)Give the control a caption

cbcCutomMenu.Caption = "&New Menu"



'(6)Working with our new Control, add a sub control and _

give it a Caption and tell it which macro to run (OnAction).

With cbcCutomMenu.Controls.Add(Type:=msoControlButton)

.Caption = "Menu 1"

.OnAction = "MyMacro1"

End With

'(6a)Add another sub control give it a Caption _

and tell it which macro to run (OnAction)

With cbcCutomMenu.Controls.Add(Type:=msoControlButton)

.Caption = "Menu 2"

.OnAction = "MyMacro2"

End With

'Repeat step "6a" for each menu item you want to add.





'Add another menu that will lead off to another menu

'Set a CommandBarControl variable to it

Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)

' Give the control a caption

cbcCutomMenu.Caption = "Ne&xt Menu"



'Add a contol to the sub menu, just created above

With cbcCutomMenu.Controls.Add(Type:=msoControlButton)

.Caption = "&Charts"

.FaceId = 420

.OnAction = "MyMacro2"

End With



End Sub





Sub DeleteMenu()

On Error Resume Next

Application.CommandBars("Worksheet Menu Bar").Controls("&New
Menu").Delete

On Error GoTo 0

End Sub



Sub MyMacro1()

MsgBox "I don't do much yet, do I?", vbInformation, "Ozgrid.com"

End Sub



Sub MyMacro2()

MsgBox "I don't do much yet either, do I?", vbInformation, "Ozgrid.com"

End SubCode to Fire off the Above Code
The code here must be placed in the Private Module of the Worksheet Object
(ThisWorkbook). To get there from within Excel proper, right click on the
Excel icon, top left next to "File", and click "View Code". In here paste
the code below;

Private Sub Workbook_Activate()

Run "AddMenus"

End Sub





Private Sub Workbook_Deactivate()

Run "DeleteMenu"

End SubThis was found at: http://www.ozgrid.com/VBA/custom-menus.htm"TJM"