超链接.add VBA

Hyperlinks.add VBA

提问by InformatikBabo

I'm programming in VBA. I want to create hyperlinks using hyperlink.addmethod of the ActiveSheet object but it doesn't work.

我正在用 VBA 编程。我想使用hyperlink.addActiveSheet 对象的方法创建超链接，但它不起作用。

Here is my code:

这是我的代码：

'set the link
Dim mainsheet As Worksheet
Dim ws As Worksheet
Set mainsheet = ActiveWorkbook.Sheets("Main")
Set ws = ActiveWorkbook.Sheets(rowNumb + 2)


mainsheet.Hyperlinks.Add Anchor:=mainsheet.Range(rowTablecontent + rowNumb, colTablecontent + 3), _
                Address:="", _
                SubAddress:=ws.Name & "!A1", _
                TextToDisplay:="Link"

Code description:

代码说明：

I have a Mainsheet, on which I have a Table of Contents and a button for starting the macro. When starting the macro, the program imports 4 Files (each has one sheet and equals one sheet in the mainworkbook. With the information of this 4 files there are about 500 sheets which are going to be generated. The point is: now i want to have a table of contents. for each sheet a link in the mainsheet.

我有一个 Mainsheet，上面有一个目录和一个用于启动宏的按钮。启动宏时，程序导入 4 个文件（每个文件有一张纸，相当于主工作簿中的一张纸。根据这 4 个文件的信息，将生成大约 500 张纸。重点是：现在我想有一个目录。对于每个工作表，主工作表中有一个链接。

Variable:

多变的：

• rowTablecontent --> Row on the top of the content table
• colTablecontent --> Column on the left of the content table
• rowNumb --> Rowcounter on the inputfile

• rowTablecontent --> 内容表顶部的行
• colTablecontent --> 内容表左侧的列
• rowNumb --> 输入文件上的行计数器

I used the same code in another program, where it worked, so I don't know what I did wrong.

我在另一个程序中使用了相同的代码，它在那里工作，所以我不知道我做错了什么。

Does anyone know what could be wrong?

有谁知道可能有什么问题？

Important Info Copied From Comments

从评论中复制的重要信息

In my project I have 4 Input files/sheets. And there it works. But on the autmatically created sheets, it doesn't work.

在我的项目中，我有 4 个输入文件/工作表。在那里它起作用了。但是在自动创建的工作表上，它不起作用。

采纳答案by Siddharth Rout

Since you have not mentioned what error you are getting. There could be many errors. Some of them are.

由于您没有提到您遇到的错误。可能有很多错误。他们之中有一些是。

1. Activesheet is not the one you think it is.
2. The selection is not a range
3. Sheet doesn't exist
4. rowNumb is not a valid number
5. Sheet/workbook could be protected (Thanks Sam)

1. Activesheet 不是您认为的那种。
2. 选择不是一个范围
3. 工作表不存在
4. rowNumb 不是有效数字
5. 可以保护工作表/工作簿（感谢 Sam）

Try this

尝试这个

Option Explicit

Sub Sample()
    Dim rowNumb As Long
    Dim ws As Worksheet

    '~~> Change this to the relevant number
    rowNumb = 1

    If TypeOf Selection Is Range Then
        On Error Resume Next
        Set ws = ThisWorkbook.Sheets(rowNumb + 1)
        If Err.Number <> 0 Then
            MsgBox "Sheet doesn't exist"
            Exit Sub
        End If
        On Error GoTo 0

        ThisWorkbook.ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
                                   Address:="", _
                                   SubAddress:=ws.Name & "!A1", _
                                   TextToDisplay:="Link"
    Else
        MsgBox "InValid Range Object"
    End If
End Sub

FOLLOWUP FROM COMMENTS

来自评论的跟进

But on the autmatically created sheets, it doesn't work.

但是在自动创建的工作表上，它不起作用。

You missed my first point. Activesheet is not the one you think it is.

你错过了我的第一点。 Activesheet is not the one you think it is.

Here is a sample on how to add hyperlink to a newly created file. This demonstration shows how to add hyperlink to Sheet1 A1of newly created file. The hyperlink will address Sheet2 A1of the newly created file.

这是有关如何向新创建的文件添加超链接的示例。这个演示展示了如何添加超链接到Sheet1 A1新创建的文件。超链接将地址Sheet2 A1新创建的文件。

Sub Sample()
    Dim wb As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet

    Set wb = Workbooks.Add

    Set ws1 = wb.Sheets("Sheet1")
    Set ws2 = wb.Sheets("Sheet2")

    ws1.Hyperlinks.Add Anchor:=ws1.Range("A1"), _
                                       Address:="", _
                                       SubAddress:=ws2.Name & "!A1", _
                                       TextToDisplay:="Link"
End Sub

I am not doing any error handling. i am sure you can take care of that.

我没有做任何错误处理。我相信你可以解决这个问题。

回答by jacouh

I tested this, it worked on Excel 2007, you could try?

我测试了这个，它在 Excel 2007 上工作，你可以试试吗？

EDIT to approach to your question.

编辑以解决您的问题。

Sub sofMacro20000463(ByVal rowNumb)
'
' Dim rowNumb
'
' rowNumb = ActiveCell.Row
'
  ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
    SubAddress:=Sheets(rowNumb + 1).Name & "!A1", _
    TextToDisplay:="Link"
'
End Sub


Sub sofMacroDoIt()
'
  Dim rowNumb
'
  rowNumb = ActiveCell.Row
'
  sofMacro20000463 rowNumb
'
End Sub

This permits navigating to Worksheet in function of row index from the activesheet.

这允许根据活动表的行索引导航到工作表。

sofMacro20000463() can be called with other parameters.

sofMacro20000463() 可以用其他参数调用。