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
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