Hello all!
I am using the following code to create an index of the worksheets that updates automatically and it works well. I want to change it to do 2 additional things... 1) alphabetize the list of hyperlinks (not the tabs themselves), and 2) format the text of the hyperlink differently (14 pt, century gothic), I am a novice and got this code elsewhere, so easy to understand responses with actual code that I can cut/paste is appreciated! P.S. I'm using a Mac.
Thank you!
Christine
Private Sub Worksheet_Activate()
'Define variables
Dim ws As Worksheet
Dim row As Long
row = 1
'Clear the previous list and add "WORKSHEET INDEX" title
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "Worsheet INDEX"
End With
'Loop through each sheet to add a corresponding hyperlink by using the name of the worksheet
For Each ws In Worksheets
If ws.Name <> Me.Name And ws.Visible = xlSheetVisible Then
row = row + 1
Me.Hyperlinks.Add Anchor:=Me.Cells(row, 1), _
Address:="", _
SubAddress:="'" & ws.Name & "'!A1", _
ScreenTip:="Click to go to sheet " & ws.Name, _
TextToDisplay:=ws.Name
End If
Next ws
'Adjust the width of first column by the longest worksheet name
Me.Columns(1).AutoFit
End Sub
