VBA to hide and Unhide All HyperLinks

Thanks for your thorough examples. How to hide and unhide Hyperlinks in a worksheet or range using VBA. I want to hide all the hyperlinks before printing a worksheet.

I want to hide hyperlinks when I print the worksheet.
My approach is/was to collect all the hyperlinks
of the worksheet in an array, then set the name in each
hyperlink to spaces. After printing I will go back and
restore the hyperlink names from the array.

Here is my testing code:

ReDim hArray(1 To ActiveSheet.Hyperlinks.Count) As Variant
Debug.Print “=.=.=.=.=.=.=.=.=.=.=.=.=.=.=”
Debug.Print “active worksheet ” & ActiveSheet.Name
n = 1 ‘hArray is base 1
Set WorkRng = ActiveSheet.UsedRange ‘range to search = the entire worksheet
For Each Rng In WorkRng ‘search for hyperlink cells
If Rng.Hyperlinks.Count > 0 Then ‘this cell refers to a hyperlink
hArray(n) = Rng.Hyperlinks(1).Name
Debug.Print n & “___________________________”
Debug.Print “name in array ” & hArray(n)
Debug.Print “name before ” & Rng.Hyperlinks(1).Name
Debug.Print “text before ” & Rng.Hyperlinks(1).TextToDisplay
Rng.Hyperlinks(1).TextToDisplay = “”
Debug.Print “text after ” & Rng.Hyperlinks(1).TextToDisplay
‘Rng.Hyperlinks(1).Name = “”
‘Debug.Print “name after ” & Rng.Hyperlinks(1).Name
n = n + 1
End If
Next

Debug output:
=.=.=.=.=.=.=.=.=.=.=.=.=.=.=
active worksheet Sheet1
1___________________________
name in array Auto(1)
name before Auto(1)
text before Auto(1)
text after Auto(1)
2___________________________
name in array Auto(2)
name before Auto(2)
text before Auto(2)
text after Auto(2)
3___________________________
name in array Condo Declarations
name before Condo Declarations
text before Condo Declarations
text after Condo Declarations
4___________________________
name in array Payment
name before Payment
text before Payment
text after Payment
5___________________________
name in array Invoice
name before Invoice
text before Invoice
text after Invoice
================================

1. Notice that Rng.Hyperlinks(1).Name = “” is commented out. When
I un-comment that I get “wrong number of arguments or invalid property assignment”
2. Although Rng.Hyperlinks(1).TextToDisplay = “” does not abort, the
hyperlink label is not actually cleared..

Thanks-Phil!

Expert Asked on July 16, 2015 in VBA: Macros.
Add Comment
1 Answer(s)
Best answer

Hi Phil!

The best way to hide the hyperlinks while printing is, changing the number format:

RE: VBA to hide and Unhide All HyperLinks

How to hide all links in the Worksheet Manually

  1. Select all Cells with Hyperlinks
  2. Right click and click on ‘Format Cells’
  3. Now go to Custom formats
  4. And enter ‘;;;’ in Type.
  5. This will hide the content/links in selected cells
  6. You can reset by changing the format to ‘General’

VBA to hide all links in a Worksheet

The below VBA code will hide all links in the active worksheet.

Sub sbHideLinksInWorksheet()
For Each lnk In ActiveSheet.Hyperlinks
lnk.Range.NumberFormat = ";;;"
Next
End Sub

How this works?:
We are looping through the all links in the worksheet. And changing its format to “;;;”.  This makes your cell content will be in visible.

VBA to Unhide all links in a Worksheet

The below VBA code will unhide all links in the active worksheet.

Sub sbUnHideLinksInWorksheet()
For Each lnk In ActiveSheet.Hyperlinks
lnk.Range.NumberFormat = "General"
Next
End Sub

How this works?:
We are looping through the all links in the worksheet. And changing its format to “General”.  This makes your cell content will be visible.

VBA to hide all links in a Range

The below VBA code will hide all links in the required range.

Sub sbHideLinksInRange()
For Each lnk In Range("A1:D10").Hyperlinks
lnk.Range.NumberFormat = ";;;"
Next
End Sub

How this works?:
We are looping through the all links in the given range. And changing its format to “;;;”.  This makes your cell content will be in visible.

VBA to Unhide all links in a Range

The below VBA code will hide all links in the required range.

Sub sbHideLinksInUnRange()
For Each lnk In Range("A1:D10").Hyperlinks
lnk.Range.NumberFormat = "General"
Next
End Sub

How this works?:
We are looping through the all links in the given range. And changing its format to “General”.  This makes your cell content will be visible.

Thanks-PNRao!

Keymaster Answered on July 16, 2015.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.