Excel VBA to make hyperlink for active cell

问题:

I want to make a link from active cell in workbook 1 which can I use it in workbook 2. I use the following code which assigned to a button:

 With ActiveSheet
   .Hyperlinks.Add Range("F6"), _
      .Parent.FullName & "#'" & .Name & "'!" & "$A$1", TextToDisplay:="link"
 End With

This code made a link with full path and I can use it in any workbook but I need some changes which I could to:

1. Make the active cell hyperlink not cell A1 which specified in code.
2. The value in the active cell become text to display arg of hyperlink function.

Thanks

PS after Vityata answere: how can i change Range("F6") to activecell adress?

回答1:

In order to obtain the active cell value and address, change your code the corresponding places with the following:

ActiveCell.Address
ActiveCell.Value

回答2:

I find it just to close this topic.

Sub Button36_Click()
Dim newRange As Range
Set newRange = Range(ActiveCell, ActiveCell.Offset(numRows, numCols))
     With ActiveSheet
   .Hyperlinks.Add Anchor:=newRange, _
      Address:=.Parent.FullName & "#'" & .Name & "'!" & ActiveCell.Address, TextToDisplay:=ActiveCell.Text
      End With
End Sub

回答3:

try this

Sub add_links_Input_Column()
Dim lRow As Long
Dim ColHead As String

ColHead = InputBox("Enter Column Letter", "Identify Column", [c1].Value)
If ColHead = "" Then Exit Sub

    With ActiveSheet
        lRow = .Range(ColHead & .Rows.Count).End(xlUp).Row
        For Each c In .Range(ColHead & "2:" & ColHead & lRow)
            ActiveSheet.Hyperlinks.Add anchor:=c, Address:=c.Value
        Next
    End With

End Sub