vba 分配键盘快捷键来运行程序

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/9818047/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 12:48:53  来源:igfitidea点击:

Assign keyboard shortcut to run procedure

excelvbakeyboard-shortcuts

提问by user1283776

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.

当我在 VBA 编辑器中按 F5 时,我总是想运行我的“Sub Skynet()”程序。有什么方法可以为此过程分配键盘快捷键。

采纳答案by user1957196

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 Returnspecifies the key and get_detailis the procedure name.

这里Return指定键,get_detail是过程名。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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

End Sub

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

现在在此工作表中,只要您按下Enter按钮,就会调用指定的宏。

回答by Glider Guider

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.
  1. 在工具菜单上,指向宏,然后单击宏。
  2. 在“宏名称”框中,输入要分配给键盘快捷键的宏的名称。
  3. 单击选项。
  4. 如果要通过按键盘快捷键来运行宏,请在“快捷键”框中输入一个字母。您可以使用CTRL+ 字母(小写字母)或CTRL++SHIFT字母(大写字母),其中字母是键盘上的任何字母键。快捷键不能使用数字或特殊字符,例如@#

    注意:当包含宏的工作簿处于打开状态时,快捷键将覆盖任何等效的默认 Microsoft Excel 快捷键。

  5. 如果要包含宏的说明,请在“说明”框中键入它。

  6. 单击确定。
  7. 单击取消。

回答by wolfstevent

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.

以下是如何在 Word 2013 中为自定义宏分配键盘快捷方式。假设您创建了一个名为“fred”的宏,并且您希望通过键入 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.
  1. 单击文件,选项。
  2. 单击自定义功能区(从我的角度来看,这是非直观的步骤)。
  3. 单击“键盘快捷键:自定义”按钮。
  4. 在类别列表框中向下滚动到按钮并选择宏。
  5. 宏列表框现在应该显示自定义宏列表。选择“弗雷德”。
  6. 单击“按新快捷键”文本框以使其处于活动状态。
  7. 键入 Ctrl+f。这应该出现在文本框中。
  8. 查看“当前键”列表框。在这种情况下,它显示“当前分配给 NavPaneSearch”。
  9. 如果您不介意覆盖该默认值,请单击左下角的“分配”按钮以分配“Ctrl+f:”来运行您的“fred”宏。

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.

默认情况下,分配保存在 Normal.dotm 文档模板中。如果此键盘分配对于此文档是唯一的,那么您可能希望将“保存更改”下拉列表更改为您的文档名称。

回答by kadrleyn

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

F函数键(F1F2F3F4F5等等)可以被分配到具有以下码的宏:

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

回答by Heman

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.

F5 是在 VBA 编辑器中运行宏的标准快捷方式。我认为您不能在编辑器本身中添加快捷键。如果要从 excel 运行宏,可以从那里分配快捷方式。

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.

在excel中按alt+F8打开宏对话框。选择要为其分配快捷键的宏并单击选项。在那里您可以为宏指定快捷方式。

回答by Chicago Joe

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.

我自己遇到了这个问题。我唯一的解决方案是先在 excel 工作簿中记录宏。然后,将模块从打开的工作簿拖放到加载项模块中。这将是上述模块的副本,但幸运的是,您分配给它的键盘快捷键将保留。

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!!!

弄清楚这一点感觉太棒了,我觉得我必须回复我在这个主题上找到的 5 年前的帖子!!!

回答by Paul

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

我在上面遇到的问题是我想将快捷键与没有可见界面的 xlam 中的宏相关联。我发现以下工作

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

在 Excel(不是 VBA)中,在 Developer 选项卡上单击 Macros - 不会显示宏 输入 Sub 的名称 然后应启用 Options 按钮 单击它 Ctrl 将是默认设置 按住 Shift 并按您想要的字母,例如 Shift 和A 将 Ctrl-Shift-A 与 Sub 相关联

回答by PhilB

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:

您可以在 VBA 编辑器环境中添加一些 ALT+Letter 快捷方式。我添加了 ALT+C 来注释选定的文本行,添加 A+X 来取消注释选定的文本行:

  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.
  1. 在 VBE 中,右键单击工具栏并选择自定义
  2. (可选)创建一个新工具栏
  3. 在类别列表框中选择命令选项卡和“编辑”。在命令列表框中找到“评论块”。单击并将其拖动到目标工具栏。左键单击它以打开上下文弹出菜单并选择“图像和文本”。用 &C 替换名称。
  4. 对 Uncomment Block 命令重复步骤 3,并将名称替换为 &X。
  5. 关闭自定义弹出窗口。

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.

内置的 Alt+Letter 命令不能用于使用字母的新快捷键:A、D、E、D、H、I、O、Q、R、T、V、W。

回答by phil borer

Write a vba proc like:

写一个 vba proc,如:

Sub E_1()
    Call sndPlaySound32(ThisWorkbook.Path & "\e1.wav", 0)
    Range("AG" & (ActiveCell.Row)).Select 'go to column AG in the same row
End Sub

then go to developer tab, macros, select the macro, click options, then add a shortcut letter or button.

然后转到开发人员选项卡,宏,选择宏,单击选项,然后添加快捷方式字母或按钮。