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:=msoCont rolButton).Caption =
"MyMenu"
Application.CommandBars("Worksheet menu
bar").Controls("Tools").Controls("MyMenu").OnActio n = 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.

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

B


"Kent McPherson" wrote in message
...
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:=msoCont rolButton).Caption =
"MyMenu"
Application.CommandBars("Worksheet menu
bar").Controls("Tools").Controls("MyMenu").OnActio n = 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.

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.

In article ,
"Kent McPherson" wrote:

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:=msoCont rolButton).Caption =
"MyMenu"
Application.CommandBars("Worksheet menu
bar").Controls("Tools").Controls("MyMenu").OnActio n = 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.

This was very helpful. Thanks.

"JE McGimpsey" wrote in message
...
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.

In article ,
"Kent McPherson" wrote:

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:=msoCont rolButton).Caption =
"MyMenu"
Application.CommandBars("Worksheet menu
bar").Controls("Tools").Controls("MyMenu").OnActio n = 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.

And you have another response at your other thread.

Kent McPherson wrote:

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:=msoCont rolButton).Caption =
"MyMenu"
Application.CommandBars("Worksheet menu
bar").Controls("Tools").Controls("MyMenu").OnActio n = 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.

--

Dave Peterson