1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by KC E, Nov 8, 2018.

  1. KC E

    KC E Member

    Messages:
    39
    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 (vb):
    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 (vb):
    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.)
  2. Kenneth Hobson

    Kenneth Hobson Active Member

    Messages:
    225
    I did one for you.
    Code (vb):
    Sub Main()
      With ActiveSheet
        .Hyperlinks.Add Selection, "", .Name & "!" & _
          ActiveCell.Offset(0, 18).Address, "Jump"
      End With
    End Sub
  3. KC E

    KC E Member

    Messages:
    39
    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 (vb):
    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 (vb):
    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.
  4. Kenneth Hobson

    Kenneth Hobson Active Member

    Messages:
    225
    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 (vb):
    Sub Main()
      With ActiveSheet
        .Hyperlinks.Add Selection, "", .Name & "!" & _
          ActiveCell.Offset(0, 18).Address, ,"Jump"
      End With
    End Sub
  5. KC E

    KC E Member

    Messages:
    39
    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 (vb):
    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

Share This Page