Hi All
I am trying to set up some hyperlinks for each cell in a range comprising
the names of a set of worksheets in the same workbook. The links are
established but the references fail to link to the worksheets. Help!!
I would also like to link to the sheet codenames not the names to protect
the links in case users change the sheet tabs. Codenames will be set to the
contents of the cell range.

Code so far......

With wbReport
For xr = 8 to 18
.Hyperlinks.Add Anchor:=.Cells(xr, 2), _
Address:=.Cells(xr, 2).Text, _
SubAddress:=.Cells(xr, 2).Text & "!A1", _
TextToDisplay:=.Cells(xr, 2).Text, _
ScreenTip:="Goto " & .Cells(xr, 2).Text & " Report"
Next xr
End With
--
Cheers
Nigel

Nigel wrote:
Hi All
I am trying to set up some hyperlinks for each cell in a range
comprising the names of a set of worksheets in the same workbook.
The links are established but the references fail to link to the
worksheets. Help!!
I would also like to link to the sheet codenames not the names to
protect the links in case users change the sheet tabs. Codenames
will be set to the contents of the cell range.

Code so far......

With wbReport
For xr = 8 to 18
.Hyperlinks.Add Anchor:=.Cells(xr, 2), _
Address:=.Cells(xr, 2).Text, _
SubAddress:=.Cells(xr, 2).Text & "!A1", _
TextToDisplay:=.Cells(xr, 2).Text, _
ScreenTip:="Goto " & .Cells(xr, 2).Text & " Report"
Next xr
End With

Nigel:

As far as I know, you can't do it with the the CodeName. One option you
have is to use the FollowHyperlink event to trick the user into thinking
they're using a hyperlink.

Anchor = .Cells(xr,2), _
Address:="", _
SubAddress = .Cells(xr,2).Address(,,,True), _
TextToDisplay:= .Cells(xr,2).Text, _
ScreenTip:=.Cells(xr,2).Text

This will create a hyperlink that points to itself. That is, it doesn't do
anything except fire the FollowHyperlink event. The FollowHyperlink event
looks like this

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

On Error Resume Next
Me.Parent.Sheets(CodeToFriendly(Target.ScreenTip, Me.Parent)).Activate

End Sub

and CodeToFriendly should go in a standard module

Function CodeToFriendly(sCode As String, wb As Workbook) As String

On Error Resume Next
CodeToFriendly = wb.VBProject.VBComponents(sCode).Properties("Name" )

End Function

Pretty kludgy, but it should work.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com



.Hyperlinks.Add .Range("A1"), "", .Address(, , , True), .Text

Thanks Dick. I will explore this option. Is there any obvious reason why my
original code fails to set up a valid reference in the hyperlink?

If I record the manual steps I get a template code that apart from my
reference changes looks OK to me. But when I clcik them I get a 'Cannot
open the specfied File' - which suggests an external link has been
established?

--
Cheers
Nigel



"Dick Kusleika" wrote in message
...
Nigel wrote:
Hi All
I am trying to set up some hyperlinks for each cell in a range
comprising the names of a set of worksheets in the same workbook.
The links are established but the references fail to link to the
worksheets. Help!!
I would also like to link to the sheet codenames not the names to
protect the links in case users change the sheet tabs. Codenames
will be set to the contents of the cell range.

Code so far......

With wbReport
For xr = 8 to 18
.Hyperlinks.Add Anchor:=.Cells(xr, 2), _
Address:=.Cells(xr, 2).Text, _
SubAddress:=.Cells(xr, 2).Text & "!A1", _
TextToDisplay:=.Cells(xr, 2).Text, _
ScreenTip:="Goto " & .Cells(xr, 2).Text & " Report"
Next xr
End With

Nigel:

As far as I know, you can't do it with the the CodeName. One option you
have is to use the FollowHyperlink event to trick the user into thinking
they're using a hyperlink.

Anchor = .Cells(xr,2), _
Address:="", _
SubAddress = .Cells(xr,2).Address(,,,True), _
TextToDisplay:= .Cells(xr,2).Text, _
ScreenTip:=.Cells(xr,2).Text

This will create a hyperlink that points to itself. That is, it doesn't
do
anything except fire the FollowHyperlink event. The FollowHyperlink event
looks like this

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

On Error Resume Next
Me.Parent.Sheets(CodeToFriendly(Target.ScreenTip, Me.Parent)).Activate

End Sub

and CodeToFriendly should go in a standard module

Function CodeToFriendly(sCode As String, wb As Workbook) As String

On Error Resume Next
CodeToFriendly = wb.VBProject.VBComponents(sCode).Properties("Name" )

End Function

Pretty kludgy, but it should work.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com



.Hyperlinks.Add .Range("A1"), "", .Address(, , , True), .Text

OK I have fixed it. The subaddress needs to be a string wrapped in quotes
like

SubAddress:="'".Cells(xr, 2).Text & "'!A1",

Resolves to: 'Sheetname!A1'

Thanks for your help. I am still assessing the codename option as I am
concerned about users changing sheet tabs! One thought is to reset the tab
names from the codenames whenever a link is clicked - not sure if this is
possible?
--
Cheers
Nigel



"Nigel" wrote in message
...
Thanks Dick. I will explore this option. Is there any obvious reason why
my
original code fails to set up a valid reference in the hyperlink?

If I record the manual steps I get a template code that apart from my
reference changes looks OK to me. But when I clcik them I get a 'Cannot
open the specfied File' - which suggests an external link has been
established?

--
Cheers
Nigel



"Dick Kusleika" wrote in message
...
Nigel wrote:
Hi All
I am trying to set up some hyperlinks for each cell in a range
comprising the names of a set of worksheets in the same workbook.
The links are established but the references fail to link to the
worksheets. Help!!
I would also like to link to the sheet codenames not the names to
protect the links in case users change the sheet tabs. Codenames
will be set to the contents of the cell range.

Code so far......

With wbReport
For xr = 8 to 18
.Hyperlinks.Add Anchor:=.Cells(xr, 2), _
Address:=.Cells(xr, 2).Text, _
SubAddress:=.Cells(xr, 2).Text & "!A1", _
TextToDisplay:=.Cells(xr, 2).Text, _
ScreenTip:="Goto " & .Cells(xr, 2).Text & " Report"
Next xr
End With

Nigel:

As far as I know, you can't do it with the the CodeName. One option you
have is to use the FollowHyperlink event to trick the user into thinking
they're using a hyperlink.

Anchor = .Cells(xr,2), _
Address:="", _
SubAddress = .Cells(xr,2).Address(,,,True), _
TextToDisplay:= .Cells(xr,2).Text, _
ScreenTip:=.Cells(xr,2).Text

This will create a hyperlink that points to itself. That is, it doesn't
do
anything except fire the FollowHyperlink event. The FollowHyperlink
event
looks like this

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

On Error Resume Next
Me.Parent.Sheets(CodeToFriendly(Target.ScreenTip,
Me.Parent)).Activate

End Sub

and CodeToFriendly should go in a standard module

Function CodeToFriendly(sCode As String, wb As Workbook) As String

On Error Resume Next
CodeToFriendly = wb.VBProject.VBComponents(sCode).Properties("Name" )

End Function

Pretty kludgy, but it should work.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com



.Hyperlinks.Add .Range("A1"), "", .Address(, , , True), .Text

Thanks for your help. I am still assessing the codename option as I am
concerned about users changing sheet tabs! One thought is to reset
the tab names from the codenames whenever a link is clicked - not
sure if this is possible?


You could do that in the FollowHyperlink event, but I'm not so sure it's a
good idea. If you don't want them to change the sheet names, you could use
protection or you could hide the sheet tabs. Those would be better options,
I think, than checking for changed sheet names..

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com