• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel VBA: Using Offset with Hyperlink

KC E

Member
Hello,

I want to be able to add hyperlinks to 4 cells in a worksheet in the same row (except for one link) across columns.


Details:

- I want to select a cell in column D and have it add a hyperlink to that active cell called ‘Jump’ that will jump to a cell in column V--still in the same row.

- From column V, I need another hyperlink called ‘Further’ to jump to a cell in column AE--still in the same row. In column V, 1 row beneath, I also need another hyperlink that says 'Home' to jump back to a cell in column A--still in the same row.

- From column AE, I need another hyperlink called ‘End/Home’ to jump back to a cell in column A--still in the same row.

Column A....................Column D..............Column V...............Column AE

(column A is home).........'Jump'...................'Further'..................'End/Home'

(column A is home)......................................'Home'..................................


What I’ve done:

1) I recorded a macro and came up with the code below. It adds the text, 'Jump', 'Further', 'End/Home', and 'Home' where it is supposed to and makes them hyperlinks, as well, but when I click the hyperlinks it says 'Reference Not Valid'. The debugger just highlights the whole row of code but I think the error is in the SubAddress—I don’t know how to structure it using Offset.

Code:
Sub SHyperlinks()

Dim sht As Worksheet

Set sht = ActiveSheet

'Add hyperlinks starting in active cell on same row across columns D, V, and AE
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    sht.Name & "!ActiveCell.Address.Offset(0, 18)", TextToDisplay:="Jump"
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset(0, 18), Address:="", SubAddress:= _
    sht.Name & "!ActiveCell.Address.Offset(0, 9)", TextToDisplay:="Further"
 
''End/Home' jumps back column A to cell in same row
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset(0, 27), Address:="", SubAddress:= _
    sht.Name & "!ActiveCell.Address.Offset(0, -30)", TextToDisplay:="End/Home"

'Add hyperlink 1 row under 'Further' in column V to go back home to cell in column A in same row
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset(1, 18), Address:="", SubAddress:= _
    sht.Name & "!ActiveCell.Address.Offset(0, -21)", TextToDisplay:="Home"


End Sub

2) I’ve also tried code using the Hyperlink function in VBA like below. It adds ‘Jump’ to the active cell as a hyperlink and puts this formula in that cell =HYPERLINK($V$23,"Jump") but it doesn’t jump to the cell V23. I think I need to add #Sheet2! in front of $T$15 but don’t know how to format it.

Also, I don’t know how to keep adding the formula/hyperlink to column V and column AE.

Code:
Sub QCHyperlinks()


Dim c As Range

Set c = ActiveCell

  With ActiveCell
  .Formula = "=HYPERLINK(" & c.Offset(0, 18).Address & "," & """Jump""" & ")"
  End With

End Sub

Can someone help me fix one of these codes? I would appreciate the help. Thank you in advance.
(The Office forum couldn't help and it has been a week since I asked.)
 

Kenneth Hobson

Active Member
I did one for you.
Code:
Sub Main()
  With ActiveSheet
    .Hyperlinks.Add Selection, "", .Name & "!" & _
      ActiveCell.Offset(0, 18).Address, "Jump"
  End With
End Sub
 

KC E

Member
I did one for you.
Code:
Sub Main()
  With ActiveSheet
    .Hyperlinks.Add Selection, "", .Name & "!" & _
      ActiveCell.Offset(0, 18).Address, "Jump"
  End With
End Sub
Thank you for your reply. I tried your code to populate just one cell and it doesn't work. I will list what it does:
- it puts the sheet name and cell reference in the cell and not the text, 'Jump'
- when I click on the link it gives the same error I got before, 'Reference not valid'
- also I tried adding more code to add hyperlinks in the other columns on the same row and it doesn't add the text or links at all. How would I format the code to include the other cells I need to add hyperlinks to?

To add a link in just one other cell in the 2nd column I tried:
Code:
Sub Main()
  With ActiveSheet
    .Hyperlinks.Add Selection, "", .Name & "!" & _
      ActiveCell.Offset(0, 18).Address, "Jump"
  .Hyperlinks.Add Selection, "", .Name & "!" & _
      ActiveCell.Offset(0, 27).Address, "Further"
  End With
End Sub
And I tried:
Code:
Sub Main()
With ActiveSheet
    .Hyperlinks.Add Selection, "", .Name & "!" & _
      ActiveCell.Offset(0, 18).Address, "Jump"
End With
With ActiveSheet
    .Hyperlinks.Add Selection, "", .Name & "!" & _
      ActiveCell.Offset(0, 27).Address, "Further"
End With
End Sub
I appreciate any help from you or anyone. Thank you.
 

Kenneth Hobson

Active Member
I don't know why it doesn't "work" for you.

To get the texttodisplay parameter set without the parameter names, just add an extra comma. e.g.
Code:
Sub Main()
  With ActiveSheet
    .Hyperlinks.Add Selection, "", .Name & "!" & _
      ActiveCell.Offset(0, 18).Address, ,"Jump"
  End With
End Sub
 

KC E

Member
I don't know why it doesn't "work" for you.

To get the texttodisplay parameter set without the parameter names, just add an extra comma. e.g.
Code:
Sub Main()
  With ActiveSheet
    .Hyperlinks.Add Selection, "", .Name & "!" & _
      ActiveCell.Offset(0, 18).Address, ,"Jump"
  End With
End Sub
Thank you very much, Kenneth. That works! I just expanded it to add the links to the other cells. Thank you, again, very much.

Code:
Sub Main()
  With ActiveSheet
    .Hyperlinks.Add Selection, "", .Name & "!" & _
      ActiveCell.Offset(0, 18).Address, , "Jump"
    .Hyperlinks.Add Selection.Offset(0, 18), "", .Name & "!" & _
      ActiveCell.Offset(0, 27).Address, , "Further"
    .Hyperlinks.Add Selection.Offset(0, 27), "", .Name & "!" & _
      ActiveCell.Offset(0, -3).Address, , "EndHome"
    .Hyperlinks.Add Selection.Offset(1, 18), "", .Name & "!" & _
      ActiveCell.Offset(1, -3).Address, , "Home"
  End With
End Sub
 
Top