vba 以编程方式创建指向工作表的超链接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17688912/
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
Programmatically creating a hyperlink to a worksheet
提问by Han P
I have been working on this for a while, I don't really know how to use vba, and I've been looking for a solution on the web for a while without success. Here is what I want to do:
我已经为此工作了一段时间，我真的不知道如何使用vba，并且我一直在网上寻找解决方案有一段时间没有成功。这是我想要做的：
I have a macro that will copy a worksheet (named "PartSheet") and put it at the end of the list of worksheets, so if I keep pressing the macro it will create pages as follow:
我有一个宏可以复制工作表（名为“PartSheet”）并将其放在工作表列表的末尾，因此如果我一直按宏，它将创建如下页面：
PartSheet (2), PartSheet (3) for the tab name and so on.
PartSheet (2)、PartSheet (3) 用于选项卡名称等。
Now I am trying to add in a hyperlink into this macro so that each time I create a new copy it will also create a hyperlink to the first worksheet (named "Summary")
现在我试图在这个宏中添加一个超链接，这样每次我创建一个新副本时，它也会创建一个指向第一个工作表（名为“摘要”）的超链接
Here is the code I have written:
这是我写的代码：
Sub Test2()
Sheets(Sheets.Count).Select
Name = ActiveSheet.Name
Sheets("Summary").Select
Range("A10").Select
ActiveCell.FormulaR1C1 = ""
Range("A10").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
" 'Name' !A1 ", TextToDisplay:="Link"
End Sub
When I run the macro, it will create the hyperlink, but it will be a invalid reference.
当我运行宏时，它会创建超链接，但它将是一个无效的引用。
回答by Jon Crowell
Replace your code with the following. Make sure you call it immediately after you make a fresh copy of your PartSheet.
将您的代码替换为以下内容。确保在制作 PartSheet 的新副本后立即调用它。
Sub AddHyperlink()
Dim targetSheet As Worksheet
Dim targetRange As Range
Dim linkedSheet As Worksheet
Dim linkRange As Range
'set variable to the sheet the hyperlink will link to
Set targetSheet = ThisWorkbook.Sheets(ActiveSheet.Name)
' specify the range on the summary sheet to link to
Set targetRange = targetSheet.Range("A1")
' set variable to sheet that will have the hyperlink
Set linkedSheet = ThisWorkbook.Sheets("Summary")
' specify where on that sheet we'll create the hyperlink
Set linkRange = linkedSheet.Range("B4")
' create the hypperlink on the copied sheet pointing
' back to the summary sheet
linkedSheet.Hyperlinks.Add Anchor:=linkRange, Address:="", SubAddress:= _
"'" & targetSheet.Name & "'!" & targetRange.Address, _
TextToDisplay:="Go To " & targetSheet.Name
End Sub
Bug-avoiding tips:
避免错误提示：
- Always avoid selecting or activating anything when writing Excel VBA code.
- Always require variable declaration (in the VBE, Tools --> Options, check the box next to Require Variable Declaration.
- 编写 Excel VBA 代码时，请始终避免选择或激活任何内容。
- 始终需要变量声明（在 VBE 中，工具 --> 选项，选中需要变量声明旁边的框。
回答by Ripster
It is not working because of how "SubAddress" is setup. Change it to this:
由于“子地址”的设置方式，它不起作用。改成这样：
Sub Test2()
Dim Name As String
Sheets(Sheets.Count).Select
Name = ActiveSheet.Name
Sheets("Summary").Select
Range("A10").Select
ActiveCell.FormulaR1C1 = ""
Range("A10").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Name & "!A1", TextToDisplay:="Link"
End Sub
