Assign keyboard shortcut to run procedure

问题:

When I press F5 in the VBA editor I would always like to run my "Sub Skynet()" procedure. Is there any way to assign a keyboard shortcut to this procedure.

回答1:

For assigning a keyboard key to button on the sheet you can use this code, just copy this code to the sheet which contain the button.

Here Return specifies the key and get_detail is the procedure name.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Application.OnKey "{RETURN}", "get_detail"

End Sub

Now within this sheet whenever you press Enter button the assigned macro will be called.

回答2:

According to Microsoft's documentation

1. On the Tools menu, point to Macro, and then click Macros.
2. In the Macro name box, enter the name of the macro you want to assign to a keyboard shortcut key.
3. Click Options.

4. If you want to run the macro by pressing a keyboard shortcut key, enter a letter in the Shortcut key box. You can use CTRL+ letter (for lowercase letters) or CTRL+SHIFT+ letter (for uppercase letters), where letter is any letter key on the keyboard. The shortcut key cannot use a number or special character, such as @ or #.

   Note: The shortcut key will override any equivalent default Microsoft Excel shortcut keys while the workbook that contains the macro is open.

5. If you want to include a description of the macro, type it in the Description box.

6. Click OK.
7. Click Cancel.

回答3:

Here is how to assign a keyboard shortcut to a custom macro in Word 2013. The scenario is you created a macro named "fred" and you want to execute the macro by typing Ctrl+f.

1. Click on File, Options.
2. Click on Customize Ribbon (from my perspective this is the non-intuitive step).
3. Click "Keyboard shortcuts: Customize" button.
4. In the Categories listbox scroll down to the buttom and select Macros.
5. The Macros list box should now show the list of custom macros. Select "fred".
6. Click the "Press new shortcut key" textbox to make it active.
7. Type Ctrl+f. This should appear in the textbox.
8. Look at the "Current keys" listbox. In this case it shows "Currently assigned to NavPaneSearch".
9. If you don't mind overriding that default, click the "Assign" button on the lower-left to assign "Ctrl+f: to run your "fred" macro.

By default the assignment is saved in the Normal.dotm document template. If this keyboard assignment is unique to this document then you may wish to change the "Save changes in" dropdown to your document name.

回答4:

F5 is a standard shortcut to run a macro in VBA editor. I don't think you can add a shortcut key in editor itself. If you want to run the macro from excel, you can assign a shortcut from there.

In excel press alt+F8 to open macro dialog box. select the macro for which you want to assign shortcut key and click options. there you can assign a shortcut to the macro.

回答5:

F function keys (F1,F2,F3,F4,F5 etc.) can be assigned to macros with the following codes :

Sub A_1()
    Call sndPlaySound32(ThisWorkbook.Path & "\a1.wav", 0)
End Sub

Sub B_1()
    Call sndPlaySound32(ThisWorkbook.Path & "\b1.wav", 0)
End Sub

Sub C_1()
    Call sndPlaySound32(ThisWorkbook.Path & "\c1.wav", 0)
End Sub

Sub D_1()
    Call sndPlaySound32(ThisWorkbook.Path & "\d1.wav", 0)
End Sub

Sub E_1()
    Call sndPlaySound32(ThisWorkbook.Path & "\e1.wav", 0)
End Sub


Sub auto_open()
    Application.OnKey "{F1}", "A_1"
    Application.OnKey "{F2}", "B_1"
    Application.OnKey "{F3}", "C_1"
    Application.OnKey "{F4}", "D_1"
    Application.OnKey "{F5}", "E_1"
End Sub

回答6:

I ran into this problem myself. The only solution I have is to record the macro in an excel workbook first. Then, drag and drop THE MODULE from the open workbook into the add-in modules. This will be a copy of the above module, but the keyboard shortcut you assigned to it will thankfully persist.

I just record a garbage macro and move it in there, then copy the code from my real module afterwords.

Felt so great to figure this out, I felt like I had to reply to the 5 year old posts I found on the subject!!!

回答7:

The problem that I had with the above is that I wanted to associate a short cut key with a macro in an xlam which has no visible interface. I found that the folllowing worked

To associate a short cut key with a macro

In Excel (not VBA) on the Developer Tab click Macros - no macros will be shown Type the name of the Sub The Options button should then be enabled Click it Ctrl will be the default Hold down Shift and press the letter you want eg Shift and A will associate Ctrl-Shift-A with the Sub

回答8:

You can add some ALT+Letter shortcuts to the VBA editor environment. I added ALT+C to Comment selected text lines and A+X to Uncomment selected text lines:

1. In the VBE, right-click on a toolbar and select Customize
2. Optionally create a new toolbar
3. Select the Command Tab and 'Edit' in the Categories listbox. Find 'Comment Block' in the Commands listbox. Click and drag it to the target toolbar. Left-click on it to open the context popup menu and select 'Image and Text'. Replace the name with &C.
4. Repeat step 3 for the Uncomment Block command and replace the name with &X.
5. Close the Customize popup window.

There are built-in Alt+Letter commands that cannot be used for new shortcuts using letters: A,D,E,D,H,I,O,Q,R,T,V,W.