Thursday, January 7, 2016

Create Index Page For WorkBook

Creating The Workbook's Index With Vba Codes

         In the index page , hyperlinks are created  to all  pages of the workbook. Clicking the hyperlinks,can be went to related address.

When "ESC key" is pressed, can be returned to the index page.Index page is automatically updated when a new sheet is added.

If you add the following codes to the  which page ,index is created in this page :

Private Sub Worksheet_Activate()
Dim Page As Worksheet
Dim k, m As Integer
k = 1
m = 0
For Each Page In Worksheets
Sheets("INDEX").Cells(k, 2).Select
Sheets("INDEX").Cells(k, 1).Value = m
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Page.Name & "!A1", TextToDisplay:=Page.Name
k = k + 1
m = m + 1
Next Page
Sheets("INDEX").Cells(1, 1).Select
End Sub

No comments:

Post a Comment