PDA

View Full Version : Solved: Hyperlinks to sheet with single quote in name



Paul_Hossler
09-15-2012, 07:58 AM
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



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


Paul

Simon Lloyd
09-15-2012, 10:59 AM
Thats an odd one!, have you tried giving the workbook name a variable, maybe it will be treated differently likeDim MyBook as String
MyBook = "User's Error"

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
09-15-2012, 12:04 PM
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


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



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
09-15-2012, 01:01 PM
If the tabname has a single quote in it (for example a'b ), you must use two single quotes in the hyperlink:





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



Have no luck with the hyperlink worksheet function.

Paul_Hossler
09-15-2012, 06:39 PM
GarysStudent --

Thanks -- that seems to work very well



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


Paul

GarysStudent
09-15-2012, 07:07 PM
You are very welcome!

snb
09-16-2012, 09:51 AM
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
09-16-2012, 10:10 AM
Thanks!