Excel 2010: How to select Userform Button and activate with pressing Enter key

L

Legacy 76526

Guest
Hi All,

I have developed a Userform button in Excel 2010 using the developer icons, which when clicked on with my mouse it runs my super dooper macro. It works great!

But one thing I want to have happen is to have the choice of using the Enter key to start the macro or use the left mouse button

For example, I place data in (say) cell A1 and the userform button is in cell A2. When I place the data in A1 and press the Enter key, the cursor moves down to A2, but doesn't highlight the user button. When I press the Enter key again, the cursor moves to cell A3.

What I want is when I fill in the data in A1 and press the Enter key, the cursor moves to cell A2 and selects the button, so that when I press the Enter key again, it activates the macro.

Any ideas will be greatly appreciated. I feel that this is an Excel issue, not VBA.

Brad2007
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Hi Brad,

One way would be to use an ActiveX button on your Worksheet in A2, which would allow you to use the button's .Activate method and KeyPress event.

Place these 3 Procedures in the Sheet Code Module of the Sheet that has the ActiveX button in A2.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address <> "$A$2" Or Target.Count > 1 Then Exit Sub
    Me.CommandButton1.Activate
End Sub

Private Sub CommandButton1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii = 13 Then Call MyMacro
End Sub

Private Sub CommandButton1_Click()
    Call MyMacro
End Sub

Place this Sub in a Standard Code Module
Code:
Sub MyMacro()
    MsgBox "MyMacro is running..."
End Sub
 
Upvote 0
Hi Jerry,

Thanks for your reply.

Your coding makes sense, but it doesn't work. I have put the coding in the Sheet area and my macro in the module. Do I need to do something to the button. Single clicking on it does nothing and double clicking only opens the VBA sheet code.

Thanks again for your assistance.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top