Excel VBA创建到另一个工作表的超链接

Simple question, I hope, but research has only brought up forum posts that give a stack of code without explaining any of it.

简单的问题,我希望,但研究只提出了论坛帖子,提供了一堆代码,而没有解释任何代码。

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, there is a separate worksheet, which is named with the IP address. I want to turn the cells in column A of the contents page into hyperlinks to their corresponding sheets (cell A1 of the destination sheet).

我的工作簿的第一张就像一个内容页面。 A列中的每个单元格都包含一个IP地址字符串。对于每个IP地址,都有一个单独的工作表,使用IP地址命名。我想将内容页面的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 page to be linked to is exactly the same as the value of the cell that will become the link.

我只需要制作超链接的VBA行;我可以找出循环等。请记住,要链接的页面的名称与将成为链接的单元格的值完全相同。

6 个解决方案

#1

I recorded a macro making a hiperlink. This resulted.

我录制了一个制作hiperlink的宏。结果。

ActiveCell.FormulaR1C1 = "=HYPERLINK(""[Workbook.xlsx]Sheet1!A1"",""CLICK HERE"")"

#2

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

希望它可以帮到某人

#3

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

#4

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

#5

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

#6

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

#1