hyperlinking to charts in file

C

c1802362

When I create a file with lots of worksheets, I'll often run the following code to create an index sheet as the first tab (I also run some code that inserts the 'home' icon with a return to this page on each individual sheet).

It works great for worksheets, but I've been unable to get it to work for chart sheets. Any suggestions?

Art

**********************************
Dim shtTarget As Worksheet, iCount As Integer

iCount = 1

Cells(1, 1) = "Sheet"
Cells(1, 2) = "Name"
Cells(1, 3) = "Sheet Status"

For Each shtTarget In Worksheets

Cells(iCount + 1, 1) = iCount

ActiveSheet.Hyperlinks.Add Anchor:=Cells(iCount + 1, 2), Address:="", _
SubAddress:="'" & shtTarget.Name & "'!A1", TextToDisplay:=shtTarget.Name

If shtTarget.Visible = True Then
Cells(iCount + 1, 3) = "Visible"
Else
Cells(iCount + 1, 3) = "Hidden"
Cells(iCount + 1, 3).Font.ColorIndex = 3
End If

iCount = iCount + 1
Next shtTarget

End Sub
 
G

GS

<FWIW>
I've pretty much abandoned using an index sheet with hyperlinks, and
surrendered to using a userform-based "SheetsManager" utility in my
multi-sheet projects. Besides being able to navigate to any sheet (or
position on a sheet), the utility also provides other useful features
that I commonly use. (It pretty much allows me to edit any property
values, and execute any methods a worksheet object supports) It also
allows me to customize to fit the context of projects it's used in. The
SheetsManager dialog runs vbModeless so it's always on top.

Perhaps you might be better off taking this sort of approach...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 

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