Add Index - VBA Code Not Working

grlpower

New Member
Joined
Oct 23, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I am brand new to VBA Code. I have a workbook with many worksheets, and I want to add an index.

I tried advice from this website: Use index sheets to quickly navigate in an Excel workbook

"To create the index, follow these steps:

  1. Insert a new worksheet at the beginning of your workbook and rename it Index.
  2. Right-click on the sheet tab and select View Code.
  3. Enter the following code in Listing A.
  4. Press [Alt][Q] and save the workbook.
The next time you open the workbook, the Index sheet will contain a list of hyperlinks to each worksheet in the workbook. Each worksheet also contains a hyperlink back to the Index sheet. In this example, the link back to the Index is contained in cell H1, but it can be in any cell that is blank for all worksheets."

****I followed the instructions. When I reopened the workbook, the index did not appear. Nothing happened. I do not know what they mean by "Listing A". When I right-clicked on the sheet tab and selected "view code," a box automatically appeared where I could place code so that's where I put it. The box had a title of "workbookname.xlsm - Sheet268 (Code)". There are 2 dropdown menus at the top-- one says "worksheet," the other says "activate." Also, I am not sure if the suggested code was like a template and you are supposed to add something to it? I copied the code "as is."***

The suggested code is:

VBA Code:
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim M As Long
M = 1
    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With
   
    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name Then
        M = M + 1
        With wSheet
            .Range("H1").Name = "Start" & wSheet.Index
            .Hyperlinks.Add Anchor:=.Range("H1"), Address:="", SubAddress:="Index", TextToDisplay:="Back to Index"
        End With
            Me.Hyperlinks.Add Anchor:=Me.Cells(M, 1), Address:="", SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
        Next wSheet
End Sub
 
Last edited by a moderator:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

grlpower

New Member
Joined
Oct 23, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Solved.
I tried again a few more times and now it is working. I think I had to click on a different worksheet in the workbook, and then click back onto the Index worksheet.
Not exactly sure what I was doing wrong, but it's working now,.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,392
Messages
5,769,816
Members
425,574
Latest member
grimeslisa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top