New menu item action

[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.

 

[Brian]

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

B

 

[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.

 

[Kent McPherson]

This was very helpful. Thanks.

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.

 

[Dave Peterson]

And you have another response at your other thread.