Add Menu Items

[Guest]

Instead of placing buttons on my spreadsheet, I want to add custom menu
options that the user can select to execute my vba code. How is this
possible?

 

[Guest]

Mike,

A good place for this would be the workbook open event:-

For Each menubar In MenuBars
With menubar.Menus("help")
Call .MenuItems.Add("My Text", "Mymacroname")
End With
Next

This simply adds the text "My Text" to the help menu and if the user clicks
it a macro called "Mymacroname" is executed.

Mike

 

[Guest]

That is exactly what I want to do. Is is also possible to create your own
menu option on the "top-level" menu instead of adding to one of the existing
menus?

 

[Guest]

Mike,

Yes you can do that. The code below adds a menu item and 2 sub menu items
that run 2 very un-exciting macros. Also included is the nacro to ger rid of
the custom menu item:-

Sub AddMenus()
Dim Menu1 As CommandBarControl
Dim MainMenuBar As CommandBar
Dim CustomMenu As CommandBarControl
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
On Error GoTo 0
Set MainMenuBar = Application.CommandBars("Worksheet Menu Bar")
HelpMenu = MainMenuBar.Controls("Help").Index
Set CustomMenu = MainMenuBar.Controls.Add(Type:=msoControlPopup,
Before:=HelpMenu)
CustomMenu.Caption = "&New Menu"
With CustomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "This Choice"
.OnAction = "MyMacro1"
End With

With CustomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "That Choice"
.OnAction = "MyMacro2"
End With
End Sub

Sub myMacro1()
MsgBox ("You ran myMacro1")
End Sub

Sub myMacro2()
MsgBox ("You ran myMacro2")
End Sub

Sub DeleteMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete

 

[Dave D-C]

From: Mike H. <[email protected]>
[msg 1]
From: Mike H <[email protected]>
[msg 2]
From: Mike H. <[email protected]>
[msg 3]
From: Mike H <[email protected]>
[msg 4]

OK, there's a period in the the first and third.
And, there's two guys talking to each other. (Aren't there?)
My question: How is it that you both have the
same "(e-mail address removed)"?

Sorry to be off-topic. D-C