Excel Menu Click 1

[RonRepp]

Hi all:

How do I code for an Excel Menu Click event?

Application.CommandBars("Worksheet Menu bar").??

Thanks,


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[GVF]

I think you will have to add a custom procedure to a built-in CommandBarButton. When the custom procedure is called you can trap it and then run the built-in procedure.
The snippet changes the OnAction event of the built-in "File/New" command to a VBA procedure. At the end of the CustomNewCmd procedure the "File/New" command button is reset to the built-in function.

Sub TrapAClick()
Application.CommandBars("Worksheet Menu Bar").Controls(1).Controls(1).OnAction = "CustomNewCmd"
End Sub

Sub CustomNewCmd()
MsgBox "The ""File Menu/New"" command was clicked"
Application.Dialogs(xlDialogNew).Show
Application.CommandBars("Worksheet Menu Bar").Controls(1).Controls(1).OnAction = ""
End Sub

Greg