Re : Excel HyperLinks to WorkSheets & Chart-Sheets

[tkt_tang]

1. Please refer to the following code :-

Set WorkSheetShingle = ActiveSheet.Range("WrkShtIndex").Offset(1,
0).Resize(32, 1)

For Each Cel In WorkSheetShingle.Cells

ShtName = Cel.Value 'Use the Tag-Name of the Sheet ......
ShtDestinAddress = Range("A1").Address 'That is the Cell-
Address at the Destination ......

With ActiveSheet
.Hyperlinks.Add Anchor:=Cel, _
Address:="", _
SubAddress:="#" & Chr(39) & ShtName & Chr(39) & "!" &
ShtDestinAddress, _
ScreenTip:="Move to a Sheet in ActiveWorkBook.", _
TextToDisplay:=Cel.Value
End With

Next Cel

2. WorkSheetShingle is a range of 32 values showing Sheet-Names
(WorkSheets as well as Chart-Sheets).

3. The code works for the WorkSheets ; unfortunately, it stalls at
Chart-Sheets.

4. The culprit is,
SubAddress:="#" & Chr(39) & ShtName & Chr(39) & "!" &
ShtDestinAddress, _

5. Please share your experience to resolve the above.

6. Regards.

 

[Sheeloo]

Are you sure it installs at 'Chart' sheets? Also what do you mean by that?

If you have your sheetnames in the range A2:A33, your code should work fine.

For chart sheets, however, it may not work unless you have a cell "A1" on
the chart sheet...

See
http://www.lockergnome.com/windows/2005/10/31/create-an-index-of-your-excel-worksheets/
for similar code...

 

[tkt_tang]

Sheeloo Esq.,

Thank you for responding to my query.

"Are you sure it installs at 'Chart' sheets?"

I do mean it stalls ...... (meaning, Come to a Standstill)

Then, Excel prompts a warning message,

"Your formula contains an invalid external reference to a worksheet."

Regards.

 

[Sheeloo]

Your post indicated that your code was not working... Apparently the link it
creates does not work for chart sheets...

Reason: You can not link to a chart sheet since there is no underlying cell...

There is a workaround however -->
Source: http://www.ozgrid.com/Excel/hyperlinks.htm

--------------------------------------
HYPERLINK TO A CHART SHEET

Unfortunately there is no standard way to link to a Chart Sheets. In fact,
Excel will not let you do this. The workaround to this is quite simple though.

1) Add a new Worksheet.

2) On the Worksheet you would like the hyperlink to the Chart sheet on, add
a hyperlink to the new Worksheet. However, the text to display should read
something like: Spending Chart or any applicable text.

3) Activate the newly added Worksheet and go to Format>Sheet>Hide

4) Right click on the hyperlink Worksheet name tab and choose View Code. In
here paste the code below and change "Spending Chart" to suite your specific
text.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)



If Target.TextToDisplay = "Spending Chart" Then Sheets("Spending
Chart").Select



End Sub

 

[tkt_tang]

Sheeloo Esq.,

Thank you for responding to my query.

Have attempted your suggestion and it works fine indeed.

Thank you once again.

Regards.