Excel worksheet index - how to alphabetize & change text formatting

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3149390%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EExcel%20worksheet%20index%20-%20how%20to%20alphabetize%20%26amp%3B%20change%20text%20formatting%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3149390%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EHello%20all!%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20am%20using%20the%20following%20code%20to%20create%20an%20index%20of%20the%20worksheets%20that%20updates%20automatically%20and%20it%20works%20well.%20I%20want%20to%20change%20it%20to%20do%202%20additional%20things...%201)%20alphabetize%20the%20list%20of%20hyperlinks%20(not%20the%20tabs%20themselves)%2C%20and%202)%20format%20the%20text%20of%20the%20hyperlink%20differently%20(14%20pt%2C%20century%20gothic)%2C%20I%20am%20a%20novice%20and%20got%20this%20code%20elsewhere%2C%20so%20easy%20to%20understand%20responses%20with%20actual%20code%20that%20I%20can%20cut%2Fpaste%20is%20appreciated!%20P.S.%20I'm%20using%20a%20Mac.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EThank%20you!%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EChristine%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Activate()%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20'Define%20variables%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20ws%20As%20Worksheet%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20row%20As%20Long%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20row%20%3D%201%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20'Clear%20the%20previous%20list%20and%20add%20%22WORKSHEET%20INDEX%22%20title%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20With%20Me%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.Columns(1).ClearContents%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20.Cells(1%2C%201)%20%3D%20%22Worsheet%20INDEX%22%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20With%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20'Loop%20through%20each%20sheet%20to%20add%20a%20corresponding%20hyperlink%20by%20using%20the%20name%20of%20the%20worksheet%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20For%20Each%20ws%20In%20Worksheets%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20ws.Name%20%26lt%3B%26gt%3B%20Me.Name%20And%20ws.Visible%20%3D%20xlSheetVisible%20Then%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3Brow%20%3D%20row%20%2B%201%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Me.Hyperlinks.Add%20Anchor%3A%3DMe.Cells(row%2C%201)%2C%20_%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Address%3A%3D%22%22%2C%20_%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20SubAddress%3A%3D%22'%22%20%26amp%3B%20ws.Name%20%26amp%3B%20%22'!A1%22%2C%20_%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ScreenTip%3A%3D%22Click%20to%20go%20to%20sheet%20%22%20%26amp%3B%20ws.Name%2C%20_%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20TextToDisplay%3A%3Dws.Name%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3BEnd%20If%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Next%20ws%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20'Adjust%20the%20width%20of%20first%20column%20by%20the%20longest%20worksheet%20name%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Me.Columns(1).AutoFit%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EEnd%20Sub%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3149390%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Occasional Visitor

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

0 Replies