Stop Excel from Following Hyperlinks

Question

Is it possible to stop following hyperlinks in Excel? I am very open to using macros and other methods. I found the following types of "solutions" after hours of searching but they would not work:

Disable all hyperlinks by using Selection.Hyperlinks.Delete
Have Excel turn off automatic hyperlink-ing
Have a dummy sheet with the hyperlinks that links to itself and use VBA for activation on follow

These do not work since I don't own the worksheet. My job is to automate reports/actions/calculations of my coworkers using user formulas or subs. The owner of the worksheet have lots of links included in the file which they would wish to keep. I cannot just arbitrarily remove their links. But troubleshooting and programming for me is difficult since I sometimes (not often, I usually use keyboard to navigate, but sometimes) accidentally click a link and there'll be popups and all that junk. I also cannot create a dummy sheet, since the worksheet contains some 10,000 lines of data, I'm afraid it would inflate the file size. Any help would be appreciated! Thanks!

This information is very minimal and insufficient to answer your question. Please add a screenshot of some (sample) data. Or describe the sheet in more detail: are hyperlinks in fixed columns, fixed rows, some other type of identifier acompanies them? — Luuklag
Will you be passing this workbook back and forwards between users? The screentips option to hold the actual hyperlink and the visible hyperlink linking back to current cell looks like a bit of a re-design piece. — QHarr
@Luuklag I'm sorry if there's a lack of data. This is not for one specific workbook, actually, but just dealing with hyperlinks in general. But usually, the hyperlinks are in fixed columns. The way they link it is manually linked as well, so I can't just have it removed and automatically added later (i.e. not website addresses or mailto) — DWReyes
@QHarr Yes, I'm afraid that the workbook(s) are usually passed a lot between users. — DWReyes

Harassed Dad Harassed Dad · Accepted Answer · 2017-12-21T11:57:08

I assume you want to temporarily disable the hyperlinks while you're working on the file, and then re-enable them when you've finished. One possible solution would be to store them in a seperate, temporary sheet.

 Sub DisableLinks()
  Dim ws As Worksheet
  Dim ts As Worksheet
  Set ts = ActiveSheet
  Set ws = Worksheets.Add(after:=ts)

  Dim hylink As Hyperlink
  Dim destlink As Hyperlink
   For Each hylink In ts.Hyperlinks
        If hylink.Range.Value <> "" Then
            ws.Range(hylink.Range.Address) = hylink.Range.Value
            If hylink.SubAddress = "" Then

               Set destlink = ws.Hyperlinks.Add(anchor:=ws.Range(hylink.Range.Address), Address:=hylink.Address, TextToDisplay:=hylink.TextToDisplay)

            Else
                ws.Hyperlinks.Add anchor:=ws.Range(hylink.Range.Address), Address:=hylink.Address, SubAddress:=hylink.SubAddress, TextToDisplay:=hylink.TextToDisplay

            End If
        hylink.Delete
         End If
    Next hylink
    ws.Visible = xlVeryHidden
End Sub

Sub RestoreLinks()
 Dim ws As Worksheet
  Dim ts As Worksheet
  Set ts = ActiveSheet
  Set ws = Worksheets(ts.Index + 1)

  ws.Visible = xlSheetVisible
  Dim hylink As Hyperlink
   For Each hylink In ws.Hyperlinks
        If hylink.Range.Value <> "" Then
            If hylink.SubAddress = "" Then

               ts.Hyperlinks.Add anchor:=ts.Range(hylink.Range.Address), Address:=hylink.Address, TextToDisplay:=hylink.TextToDisplay
            Else
                ts.Hyperlinks.Add anchor:=ts.Range(hylink.Range.Address), Address:=hylink.Address, SubAddress:=hylink.SubAddress, TextToDisplay:=hylink.TextToDisplay

            End If
        hylink.Delete
         End If
    Next hylink

    ws.Delete
End Sub

Stop Excel from Following Hyperlinks

2 Answers