No announcement yet.

Reset the cell reference to all hyperlinks to their own cell and worksheet

  • Filter
  • Time
  • Show
Clear All
new posts

  • Reset the cell reference to all hyperlinks to their own cell and worksheet

    I was hoping someone could help me with a problem using OneDrive and excel hyperlinks.
    When a hyperlink is created with a cell reference to the same worksheet and Cell, OneDrive creates unique reference to a path of that hyperlink in the OneDrive folder.
    When a sheet is copied and renamed the path is not valid anymore creating a "Reference isn't Valid" error.
    I found this code that resets the Hyperlinks back to itself (or so I thought). But when the worksheet is copied and the original worksheet deleted the references break because they are tied to the original worksheet.
    Is it possible to use the code below and reset the links to itself? (correct worksheet name)
    Any help would be appreciated.

    Sub UpdateAllHyperLinks()
        Dim r As Range, s As String
        For Each r In Selection
        If Len(r.Text) > 1 Then
             s = r.Text
        ActiveSheet.Hyperlinks.Add Anchor:=r, Address:="", SubAddress:=r.Parent.Name & "!" & r.Address(0, 0), TextToDisplay:=s
             ' s = r.Text  /// You could have this add a value to blank cells
            End If
        Next r
    End Sub

  • #2
    Is there another macro that could achieve the same results? I am looking for a macro that can reset all hyperlinks to their same cell in a sheet (they don't have to go anywhere, just link back to their same cell).
    I use the hyperlinks along with the, Worksheet_FollowHyperlink Event to call macros.


    • #3

      I found this code that works to reset the values to their same cell without changing the cell contents and I have tried to use it in the loop above, but it hangs and does not run. It would appear that the trick is to use the SubAddress:=ActiveCell.Address. But it does not run in the code above.
      Can someone help with a loop that would work on a range such as A1:A50. Please?

      Sub HyperLinkMaker()
                  ' This code works if only 1 cell is selected
          ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:=ActiveCell.Address, TextToDisplay:=ActiveCell.Value
      End Sub