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?

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

"Mike H." wrote:

> 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?

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?

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
On Error GoTo 0
End Sub

"Mike H." wrote:

> 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?
>

From: Mike H. <(E-Mail Removed)>
[msg 1]
From: Mike H <(E-Mail Removed)>
[msg 2]
From: Mike H. <(E-Mail Removed)>
[msg 3]
From: Mike H <(E-Mail Removed)>
[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 Removed)"?

Sorry to be off-topic. D-C

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----