Solved: Hyperlinks to sheet with single quote in name

[Paul_Hossler]

Funny problem when inserting a hyperlink to another cell in the same workbook, but a different sheet

If the .Subaddress contains spaces, Excel needs single quotes around the WS name. OK

But if there's already a single quote in the WS name (e.g. User's Errors) , then the hyperlink doesn't work and doesn't take me to the cell and gives me a 'Reference not valid" error message

Is there a workaround? I'd prefer not to have to disallow single quotes, since otherwise it is an allowed character


[vba]
Option Explicit
Sub test()

Dim rLink As Range, rError As Range


Set rLink = Worksheets("Error Log").Range("A1")
Set rError = Worksheets("User's Errors").Range("C5")


Worksheets("Error Log").Hyperlinks.Add _
Anchor:=rLink, Address:="", SubAddress:="'" & rError.Parent.Name & "'!" & rError.Address, _
TextToDisplay:="This link does not work"



Set rLink = Worksheets("Error Log").Range("A4")
Set rError = Worksheets("Users Errors").Range("C5")
Worksheets("Error Log").Hyperlinks.Add _
Anchor:=rLink, Address:="", SubAddress:="'" & rError.Parent.Name & "'!" & rError.Address, _
TextToDisplay:="This link does"
End Sub
[/vba]

Paul

[Simon Lloyd]

Thats an odd one!, have you tried giving the workbook name a variable, maybe it will be treated differently like[VBA]Dim MyBook as String
MyBook = "User's Error"[/VBA]

To be honest it's bad practice to have illegal filename characters in worksheet names, as you've found out it causes many headaches down the line.

[Paul_Hossler]

Hi Simon ---

To be honest it's bad practice to have illegal filename characters in worksheet names, as you've found out it causes many headaches down the line.

1. Possibly, but a single quote seems to be an acceptable file name character (see attachment)

2. Doing it as a variable doesn't seem to make any difference

[vba]
Option Explicit
Sub test()
Dim sLinkAddress As String
Dim rLink As Range, rError As Range
Set rLink = Worksheets("Error Log").Range("A1")
Set rError = Worksheets("User's Errors").Range("C5")
sLinkAddress = "'" & rError.Parent.Name & "'!" & rError.Address
Worksheets("Error Log").Hyperlinks.Add _
Anchor:=rLink, Address:="", SubAddress:="'" & rError.Parent.Name & "'!" & rError.Address, _
TextToDisplay:="This link does not work"

Set rLink = Worksheets("Error Log").Range("A4")
Set rError = Worksheets("Users Errors").Range("C5")
sLinkAddress = "'" & rError.Parent.Name & "'!" & rError.Address
Worksheets("Error Log").Hyperlinks.Add _
Anchor:=rLink, Address:="", SubAddress:="'" & rError.Parent.Name & "'!" & rError.Address, _
TextToDisplay:="This link does"
End Sub
[/vba]


It seems to be because there is already a pair of single quotes bracketing the WS name with the spaces, so one more confuses it.

Paul

[GarysStudent]

If the tabname has a single quote in it (for example a'b ), you must use two single quotes in the hyperlink:



[vba]

Sub Macro1()
ActiveSheet.Hyperlinks.Add
Anchor:=Selection, Address:="", SubAddress:= _
"'a''b'!B9", TextToDisplay:="'a''b'!B9"
End Sub

[/vba]

Have no luck with the hyperlink worksheet function.

[Paul_Hossler]

GarysStudent --

Thanks -- that seems to work very well


[VBA]
Option Explicit
Sub test()
Dim sLinkAddress As String
Dim rLink As Range, rError As Range
Set rLink = Worksheets("Error Log").Range("A1")
Set rError = Worksheets("User's Errors").Range("C5")
sLinkAddress = "'" & Replace(rError.Parent.Name, "'", "''") & "'!" & rError.Address

Worksheets("Error Log").Hyperlinks.Add _
Anchor:=rLink, Address:="", SubAddress:=sLinkAddress, _
TextToDisplay:="Now this link DOES work"

Set rLink = Worksheets("Error Log").Range("A4")
Set rError = Worksheets("Users Errors").Range("C5")
sLinkAddress = "'" & Replace(rError.Parent.Name, "'", "''") & "'!" & rError.Address

Worksheets("Error Log").Hyperlinks.Add _
Anchor:=rLink, Address:="", SubAddress:=sLinkAddress, _
TextToDisplay:="This link does"
End Sub
[/VBA]

Paul

[GarysStudent]

You are very welcome!

[snb]

If you insert a hyperlink manually you'll will be shown the correct reference (the extra single quote).

As a worksheet function:

=HYPERLINK("#'user''s errors'!C5";"illustration") 

[GarysStudent]

Thanks!