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


"John Wirt" wrote in message
...
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

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


"John Wirt" wrote
in message
...
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


.

Well, no, the quotes are needed if the sheet name has a
blank in it. Of course.

strAddress = Replace(""" & strWshName & "'!
A1", " ", "")

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


"John Wirt" wrote
in message
...
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


.

.