CODEWITHSUNDEEP
excelvbahyperlink
cd3091
cd3091

Reputation: 87

How to Change the Name of the Hyperlink?

Hyperlink

The code below creates a hyperlink on the x's under # column, and when clicked arrives at the latest nonempty cell under the 2 PQ Columns (first x - > 'good' under 'Blah PQ19' column, 4th x -> 1 under 'Blah PQ18' column.)

I need to replace/rename the x's with either PQ18 or PQ19 (under where the hyperlink arrives to). I've attempted looking at posts to learn how to fetch the column header name and bring to change the x's but couldn't do it.. Does anyone know how to do this? 

Dim i As Integer
Dim PCell As String

For i = 2 To 5

If Not IsEmpty(Cells(i, "CT").End(xlToRight)) Then
PCell = Cells(i, "CT").End(xlToRight).Address
ActiveSheet.Hyperlinks.Add Cells(i, 2), Address:="", SubAddress:="'" & Sheet1.Name & "'!" & PCell
End If

Next i

Upvotes: 1

Views: 218

Answers (2)

JvdV
JvdV

Reputation: 75840

As per my comment you can look into the TextToDisplay parameter of the Hyperlinks.Add method:

Sample data in Range(A1:F5):

enter image description here

Code:

Sub Test()

Dim x As Long

With Sheet1 'Change according to your sheets CodeName
    For x = 2 To 5
        i = .Cells(x, "CT").End(xlToLeft).Column 'Notice xlToLeft instead to Right
        If i > 1 Then
            .Hyperlinks.Add .Cells(x, 1), "", "'" & .Name & "'!" & .Cells(x, i).Address, , .Cells(1, i).Value
        End If
    Next x
End With

End Sub

Result:

enter image description here

Upvotes: 1

braX
braX

Reputation: 11755

You can simply set the value of the cell...

Dim i As Integer
Dim PCell As String

For i = 2 To 5

  If Not IsEmpty(Cells(i, "CT").End(xlToRight)) Then
    PCell = Cells(i, "CT").End(xlToRight).Address
    ActiveSheet.Hyperlinks.Add Cells(i, 2), Address:="", SubAddress:="'" & Sheet1.Name & "'!" & PCell
    ActiveSheet.Cells(i, 2).Value = "Test " & i ' not sure what you want to set it to?
  End If

Next

Upvotes: 1

Related Questions