Hyperlink written to worksheet produces error

  • Thread starter Thread starter Alex@JPCS
  • Start date Start date
A

Alex@JPCS

John,
I think that the Address parameter refers to a file address. Try SubAddress
as a range or named range in the same workbook.

AlexJ
 
I'm writing a subroutine to list all the worksheets in a
workbook on a new worksheet called, "List of Worksheets."

Here is the code:

For Each Wsh In Sheets
strWshName = Wsh.Name
If strWshName <> "List of Worksheets" Then
I = I + 1
Set RA = Range(Replace("B" & Str(I), " ", ""))
RA.FormulaR1C1 = strWshName
strAddress = Replace("'" & strWshName & "'!
A1", " ", "")
Wsh.Hyperlinks.Add _
Anchor:=RA, _
Address:=strAddress, _
TextToDisplay:=strWshName
End If
Next Wsh

Teh problem is that when I click on the inserted links I
get an error message: "Cannot open the specified file."

Indeed, if I try to edit the hyperlink directly, the
correct sheet address (e.g. 'Sheet2'!A1) is in the address
field of the Edit Hyperlinks window but the "Link to"
selection is "Existing File or web Page" rather
than "Place in this document." How do I write the
Hyperink.Add statement specify that the hyperlink is for a
__place in the document__ and not a web address or a
filename?

Thanks.

John Wirt
 
Alex,

Thank you. Adding the sheet and cell location as a
SubAddress works. The VB Help is not very clear on this. I
found that the sheet name should be entered without
quotes, and the Address must be entered as a blank.
Otherwise, the Hyplinks.Add statement fails.

For Each Wsh In Sheets
strWshName = Wsh.Name
If strWshName <> "List of Worksheets" Then
I = I + 1
Set RA = Range(Replace("B" & Str(I), " ", ""))
'RA.FormulaR1C1 = strWshName
strAddress = Replace(strWshName & "!A1", " ", "")
ActiveSheet.Hyperlinks.Add _
Anchor:=RA, _
Address:="", _
SubAddress:=strAddress, _
TextToDisplay:=strWshName
End If
Next Wsh

John Wirt
 
Back
Top