Assigning hotkeys to buttons on forms created for excel vba

Question

Hello, I have created a macro for excel which will pop up form that contains button and text field. Is there a way to assign hotkeys such as 'ctrl+Enter' or 'F12' to the buttons? The hotkey should work regardless of which field or button the focus is on.

(so far I have managed to create buttons/fields_Keydowns to check MSForms.ReturnInteger for vbKeyF12, but I have to do that for every field and button, is there an easier way?)

Say, I have 2 things on the form, button "CommandButton1" and textfield "TextBox1"

code for the button:

Private Sub CommandButton1_click()
ActiveCell.FormulaR1C1 = UserForm1.TextBox1.Text 
End Sub

The hotkey will be useful when I add in more fields and buttons...

Also how do i set the 'Escape' button to close/hide the form ?

Answer 1

To set the 'Escape' key to activate your button to close/hide the form: First, you need a button whose Click event hides your form. Then set the 'Cancel' property of that button to True.

When your form is displayed, and you press Esc, the form will close.

For a 'HotKey', set the Accelerator property of your button to a letter, then when your form is open, if you press Alt+[your letter], the Click event of that button will fire.