New menu item action

K

Kent McPherson

I have a menu item added to the Tools menu called MyMenu. I'm trying to tie
it to a VBA subroutine I've written called MySub. I have this code for the
menu.

Application.CommandBars("Worksheet menu
bar").Controls("Tools").Controls.Add(Type:=msoControlButton).Caption =
"MyMenu"
Application.CommandBars("Worksheet menu
bar").Controls("Tools").Controls("MyMenu").OnAction = MySub

Then I created a new module with this code.

Private Sub MySub ()

MsgBox ("My subroutine")

End Sub

When I select the MyMenu command from the Tools menu, nothing happens so I'm
obviously not making the linkage correctly. Any help would be greatly
appreciated.
 
B

Brian

Did you try removing the Parens from the Msgbox line??? or Try making MySub
public.

B
 
J

JE McGimpsey

One way:

With CommandBars.FindControl(Id:=30007).Controls.Add( _
Type:=msoControlButton)
.Caption = "MyMenu"
.OnAction = "MySub"
End With

Using the Id is generally better than using the names/captions, since
names and captions are language specific, and in any case can be changed.
 
K

Kent McPherson

This was very helpful. Thanks.

JE McGimpsey said:
One way:

With CommandBars.FindControl(Id:=30007).Controls.Add( _
Type:=msoControlButton)
.Caption = "MyMenu"
.OnAction = "MySub"
End With

Using the Id is generally better than using the names/captions, since
names and captions are language specific, and in any case can be changed.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top