Shortcut to "click" a commandbutton

[UHsoccer]

I have a shortcut (Ctrl-M) that changes the display to a sheet called "Select". In addtion I would also like to have it click a commandbutton that is on the displayed sheet.
When I record a macro, it does nor record anything when I click on a commandbutton.

It is an action that is performed many, many times per day and it would reduce the number of clicks a user would have to perform.

Here is the workbook code

Private Sub Workbook_Open()
     Application.OnKey "^m", "Display_Select"  '<- Ctrl + m
End Sub

 

[Norie]

If you want to run the code behind the button why not put it in a standard module.

Then you can just call it from the code that changes the display and it can still be called when the button is clicked.

 

[QuietRiot]

wouldnt you just call the commandbutton?

Private Sub CommandButton1_Click()

End Sub

so this is commandbutton1_click

can't u just call that?

 

[Fr33dan]

You'll have to create a macro that calls both Display_Select and the sub that the button calls(right click>assign macro will let you see it's name) then set that macro to the shortcut

 

[UHsoccer]

I tried the option from Quiet Riot, but to no avail.

Here is the code in "This Workbook"

Private Sub Workbook_Open()
     Application.OnKey "^m", "Display_Select"  '<- Ctrl + m
     
     CommandButton1_Click
End Sub

 

[Smitty]

Just my 2 cents, but I'd take Norie's advice and put your code in a general module, then call it in your Open and Click events.

That's a lot easier than trying to fuddle with getting the click event to work as you envision.

HTH,

Smitty

 

[Tom Urtis]

I have a shortcut (Ctrl-M) that changes the display to a sheet called "Select". In addtion I would also like to have it click a commandbutton that is on the displayed sheet.

Just do this:

First, presumably your workbook Open event is this:

Private Sub Workbook_Open()
Application.OnKey "^m", "Display_Select"  '<- Ctrl + m
End Sub

That means you have, or should have, this for your Close or Deactivate event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "^m"
End Sub

Now, you never said what the name of your command button is on the Select sheet, but let's assume that it is named CommandButton1 and is, by inference, created from the control toolbox.

Imagine that this is your click event for that button in its worksheet module:

Public Sub CommandButton1_Click()
MsgBox "Hello"
End Sub

Important, notice that you must make this a Public statement just like i have it here.


Then, you have a sub named "Display_Select" in a standard module:

Sub Display_Select()
Sheets("Select").Activate
Call Worksheets("Select").CommandButton1_Click
End Sub

And that's it, relatively painless, works for me.

 

[UHsoccer]

The solution from Tom Urtis works as esxpeted. The code is simple and the statement to make the "Sub CommandButton1_click" Public, made the difference

Thanks for the help

 

[Tom Urtis]

A follow-up tip, if you are on a sheet other than the Select sheet when you press Ctrl+M, and then while you are on the Select sheet you press Ctrl+M again and nothing seems to happen, it will be because you need to do a one-time adjustment of changing the TakeFocusOnClick property of the Command Button to False, if you had not already done so.