在 Excel 中，我可以使用超链接来运行 vba 宏吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28728600/
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
In Excel, can I use a hyperlink to run vba macro?
提问by Jason
I have a spreadsheet that has many rows of data. I would like to be able to click on a cell that will run a macro using the data from that row. Since the number of rows will always be changing, I though a hyperlink for each row might be the best way.
我有一个包含多行数据的电子表格。我希望能够单击将使用该行中的数据运行宏的单元格。由于行数总是在变化，我认为每行的超链接可能是最好的方法。
ROW MeterID Lat Long ReadX ReadY ReadZ CoeffA CoeffB CoeffC
2 10f62gs 34.1 33.3 102.2 231.3 382.2 4.34 22.1 0.002
3 83gs72g 34.4 31.4 109.2 213.1 372.1 2.23 12.7 0.023
4 43gS128 33.3 32.2 118.8 138.7 241.8 1.94 5.08 0.107
Is there a way to run a vba macro from clicking on a hyperlink and being able to know the row of the cell that clicked on the hyperlink?
有没有办法通过单击超链接并能够知道单击超链接的单元格的行来运行 vba 宏？
回答by Jeanno
This will work for you
这对你有用
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
MsgBox "Row " & ActiveCell.Row & " is clicked"
End Sub
回答by M M
Yes you can, follow the below Simple Steps to do so:
是的，您可以，请按照以下简单步骤操作：
Step 1. Select the Cell Where you want to make the Hyperlink Step 2. Righ Click –> Hyperlink… Step 3. Enter the Address of the Same cell where you are making the hyperlink and Give name to the Link. See the below picture:
步骤 1. 选择要制作超链接的单元格 步骤 2. 右键单击 –> 超链接... 步骤 3. 输入要制作超链接的同一单元格的地址并为链接命名。见下图：
Assign Macro to a Hyperlink
将宏分配给超链接
Step 4. Click Ok. Step 5. HyperLink is created.
步骤 4. 单击确定。步骤 5. 创建超链接。
Note: Clicking on this Hyperlink, will do nothing because it is assigned to the same Cell Address.
注意：单击此超链接不会执行任何操作，因为它已分配给相同的单元格地址。
Step 6. Now Press Alt + F11 Step 7. Copy paste the below Code
步骤 6. 现在按 Alt + F11 步骤 7. 复制粘贴以下代码
Run Excel Macro by Clicking on a Hyperlink
通过单击超链接运行 Excel 宏
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
'Check if the Target Address is same as you have given
'In the above example i have taken A4 Cell, so I am
'Comparing this with $A
If Target.Range.Address = "$A" Then
'Write your all VBA Code, which you want to execute
'Or Call the function or Macro which you have
'written or recorded.
MsgBox "Write your Code here to be executed"
Exit Sub
End If
End Sub
In the Above Code we are comparing the Cell Address and then Executing a Set of Code or Function. There is another way of doing this also. We can Compare with the Target Name and execute the Code. In the above Example as i have given the Name of the Hyperlink Target as MyMacro.
在上面的代码中，我们正在比较单元格地址，然后执行一组代码或函数。还有另一种方法可以做到这一点。我们可以与目标名称进行比较并执行代码。在上面的示例中，我将超链接目标的名称指定为 MyMacro。
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
'Check if the Target Name is same as you have given
'In the above example i have given the Name of the HyperLink
'is MyMacro.
If Target.Name = "mymacro" Then
'Write your all VBA Code, which you want to execute
'Or Call the function or Macro which you have
'written or recorded.
MsgBox "Write your Code here to be executed"
Exit Sub
End If
End Sub
回答by user3561813
I think rather than go through the hassle of creating a hyperlink for each cell, you would be better off creating a macro that references the Activecellproperty. Next, create a keyboard shortcut for the macro to run. To do so:
我认为与其为每个单元格创建一个超链接的麻烦，不如创建一个引用该Activecell属性的宏。接下来，为要运行的宏创建键盘快捷键。这样做：
- Press ALT+F8
- Select
Options - Choose a key to be the shortcut key
- 按 ALT+F8
- 选择
Options - 选择一个键作为快捷键
If you already have hyperlinks, triggering a macro using the Hyperlink_Followevent may be best. If not, then consider my recommendation.
如果您已经有超链接，Hyperlink_Follow则最好使用该事件触发宏。如果没有，请考虑我的建议。
