Re : Excel HyperLinks to WorkSheets & Chart-Sheets

T

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

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

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
 
T

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top