vba 创建指向另一个工作表的超链接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9813488/
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
Create hyperlink to another sheet
提问by Swiftslide
The first sheet of my workbook is like a contents page. Each cell in column A holds an IP address string. For each IP address string, there is a worksheet named with the IP address.
我的工作簿的第一页就像一个目录页。A 列中的每个单元格都包含一个 IP 地址字符串。对于每个 IP 地址字符串，都有一个以 IP 地址命名的工作表。
I want to turn the string in the cells in column A into hyperlinks to their corresponding sheets (cell A1 of the destination sheet).
我想将 A 列中单元格中的字符串转换为指向其相应工作表（目标工作表的单元格 A1）的超链接。
I only need the VBA line that makes the hyperlink; I can figure out the looping, etc. Remember that the name of the sheet to be linked to is the same as the value of the cell that will become the link.
我只需要制作超链接的 VBA 行；我可以找出循环等。记住要链接到的工作表的名称与将成为链接的单元格的值相同。
Research has only brought up forum posts that give a stack of code without explaining any of it.
研究只提出了提供一堆代码的论坛帖子，而没有解释任何代码。
回答by curial
I recorded a macro making a hiperlink. This resulted.
我录制了一个制作超链接的宏。结果就这样了。
ActiveCell.FormulaR1C1 = "=HYPERLINK(""[Workbook.xlsx]Sheet1!A1"",""CLICK HERE"")"
回答by Lee
This is the code i use for creating an index sheet.
这是我用于创建索引表的代码。
Sub CreateIndexSheet()
Dim wSheet As Worksheet
ActiveWorkbook.Sheets.Add(Before:=Worksheets(1)).Name = "Contents" 'Call whatever you like
Range("A1").Select
Application.ScreenUpdating = False 'Prevents seeing all the flashing as it updates the sheet
For Each wSheet In Worksheets
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=wSheet.Name & "!A1", TextToDisplay:=wSheet.Name
ActiveCell.Offset(1, 0).Select 'Moves down a row
Next
Range("A1").EntireColumn.AutoFit
Range("A1").EntireRow.Delete 'Remove content sheet from content list
Application.ScreenUpdating = True
End Sub
Hope it helps someone
希望它可以帮助某人
回答by whytheq
Something like the following will loop through column A in the Control sheet and turn the values in the cells into Hyperlinks. Not something I've had to do before so please excuse bugs:
类似下面的内容将遍历控制表中的 A 列，并将单元格中的值转换为超链接。不是我以前必须做的事情，所以请原谅错误：
Sub CreateHyperlinks()
Dim mySheet As String
Dim myRange As Excel.Range
Dim cell As Excel.Range
Set myRange = Excel.ThisWorkbook.Sheets("Control").Range("A1:A5") '<<adjust range to suit
For Each cell In myRange
Excel.ThisWorkbook.Sheets("Control").Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:=cell.Value & "!A1" '<<from recorded macro
Next cell
End Sub
回答by user3423549
The "!" sign is the key element. If you have a cell object (like "mycell" in following code sample) and link a cell to this object you must pay attention to ! element.
这 ”！” 标志是关键要素。如果您有一个单元格对象（如以下代码示例中的“mycell”）并将单元格链接到此对象，您必须注意！元素。
You must do something like this:
你必须做这样的事情：
.Cells(i, 2).Hyperlinks.Add Anchor:=.Range(Cells(i, 2).Address), Address:="", _
SubAddress:= "'" & ws.Name & "'" & _
"!" & mycell.Address
回答by Ravi Biruduganti
If you need to hyperlink Sheet1 to all or corresponding sheets, then use simple vba code. If you wish to create a radio button, then assign this macro to that button ex "Home Page".
如果您需要将 Sheet1 超链接到所有或相应的工作表，请使用简单的 vba 代码。如果您想创建一个单选按钮，则将此宏分配给该按钮，例如“主页”。
Here is it:
就这个：
Sub HomePage()
'
' HomePage Macro
'
' This is common code to go to sheet 1 if do not change name for Sheet1
'Sheets("Sheet1").Select
' OR
' You can write you sheet name here in case if its name changes
Sheets("Monthly Reports Home").Select
Range("A1").Select
End Sub
回答by Constanza Garcia
This macro adds a hyperlink to the worksheet with the same name, I also modify the range to be more flexible, just change the first cell in the code. Works like a charm
这个宏给同名的工作表添加了一个超链接，我也修改了范围更灵活，只需更改代码中的第一个单元格即可。奇迹般有效
Sub hyper()
Dim cl As Range
Dim nS As String
Set MyRange = Sheets("Sheet1").Range("B16")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each cl In MyRange
nS = cl.Value
cl.Hyperlinks.Add Anchor:=cl, Address:="", SubAddress:="'" & nS & "'" & "!B16", TextToDisplay:=nS
Next
End Sub
回答by John Stowell
In my implementation, the cell I was referencing could have been several options. I used the following format where 'ws' is the current worksheet being edited
在我的实现中，我引用的单元格可能有几个选项。我使用了以下格式，其中“ws”是当前正在编辑的工作表
For each ws in Activeworkbook.Worksheets
For i…
For j...
...
ws.Cells(i, j).Value = "=HYPERLINK(""#'" & SHEET-REF-VAR & "'!" & CELL-REF-VAR & """,""" & SHEET-REF-VAR & """)"
