Indexer


hi Guys,
it took my lot of time to create home/Index tab to navigate in excel workbook, i always need it in most of dashboards ,dont you?
You can set any shape style default by right click on the shapes, it will work accordingly.
 Image
here is the code to create index : 
Sub CreateIndex()
    ‘–Code by Rajan
    Dim WksHomeTab As Worksheet
    Dim wksSheet As Worksheet
    Dim lngCOunter As Long
    lngCOunter = 0
    Set WksHomeTab = ThisWorkbook.Worksheets.Add
    With WksHomeTab
        .Name = “Home”
        For Each wksSheet In ThisWorkbook.Worksheets
            If wksSheet.Name <> “Home” Then
                .Range(“C4”).Offset(lngCOunter).Value = wksSheet.Name
                lngCOunter = lngCOunter + 1
            End If
        Next wksSheet
        .Range(“C4”).CurrentRegion.EntireRow.RowHeight = 25
        .Range(“C4”).Resize(, 2).EntireColumn.ColumnWidth = 30
        InsertShape WksHomeTab
        ActiveWindow.DisplayGridlines = False
        .Protect
    End With
End Sub
Sub InsertShape(wksHome As Worksheet)
    Dim rngCell As Range
    Dim ShpShape As Shape
    For Each rngCell In wksHome.Range(“C4”).CurrentRegion
        Set ShpShape = wksHome.Shapes.AddShape(msoShapeRectangle, rngCell.Offset(, 1).Left + 5, rngCell.Offset(, 1).Top + 5, 90, 20)
        ShpShape.TextFrame.Characters.Text = “View”
        ShpShape.TextFrame.HorizontalAlignment = xlHAlignCenter
        wksHome.Hyperlinks.Add ShpShape, “”, “‘” & rngCell.Value & “‘!A1”, “Click”, “”
        ShpShape.Locked = False
    Next rngCell
    Set ShpShape = wksHome.Shapes.AddShape(msoShapeFrame, wksHome.Range(“B1”).Top, wksHome.Range(“B1”).Left, wksHome.Range(“E1”).Left – wksHome.Range(“B1”).Left, wksHome.Range(“C4”).End(xlDown).Offset(2).Top – wksHome.Range(“B1”).Top)
    With ShpShape
        .Select
        Selection.ShapeRange.Adjustments.Item(1) = 0.06063
        Selection.ShapeRange.Adjustments.Item(1) = 0.05143
        .Top = wksHome.Range(“B2”).Top
        .Left = wksHome.Range(“B2”).Left
    End With
End Sub
you can download .xlsm from here
Thanks For Reading
You can buy indexer with more feature here

10 Comments Add yours

  1. John says:

    Hi Rajan, I can’t download the file. It wants me to install some other download management software and I don’t want this. Previously I could download your samples directly…

  2. John says:

    Hi Rajan, I got it working from your code above. It would be great to have the option to create a hyperlink back to the Home page from all the other pages (and for this to be set in a cell specified by the user – e.g. “Where would you like the return link to be located (default A1)?” Best regards…

  3. I like the example Rajan but can I suggest 2 improvements:
    1 – A pre-check to ensure that the menu does not exist, or if it does, delete the Home sheet.
    2 – Ensure the menu goes into the file as the first sheet.

    Amended Code:
    Option Explicit

    Sub CreateIndex()

    ‘–Code by Rajan
    With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    End With

    ‘ (Mike Derham) Ensure first sheet gets the menu:
    Sheets(1).Activate

    Dim WksHomeTab As Worksheet
    Dim wksSheet As Worksheet
    Dim lngCOunter As Long
    lngCOunter = 0
    Set WksHomeTab = ThisWorkbook.Worksheets.Add

    ‘ (Mike Derham) Delete any previous Home sheets:
    For Each wksSheet In ThisWorkbook.Worksheets
    If wksSheet.Name = “Home” Then Sheets(wksSheet.Name).Delete
    Next wksSheet

    With WksHomeTab
    .Name = “Home”
    For Each wksSheet In ThisWorkbook.Worksheets
    If wksSheet.Name “Home” Then
    .Range(“C4”).Offset(lngCOunter).Value = wksSheet.Name
    lngCOunter = lngCOunter + 1
    End If
    Next wksSheet
    .Range(“C4”).CurrentRegion.EntireRow.RowHeight = 25
    .Range(“C4”).Resize(, 2).EntireColumn.ColumnWidth = 30
    InsertShape WksHomeTab
    ActiveWindow.DisplayGridlines = False
    .Protect
    End With

    With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
    End With
    End Sub

    Sub InsertShape(wksHome As Worksheet)

    Dim rngCell As Range
    Dim ShpShape As Shape

    For Each rngCell In wksHome.Range(“C4”).CurrentRegion
    Set ShpShape = wksHome.Shapes.AddShape(msoShapeRectangle, rngCell.Offset(, 1).Left + 5, rngCell.Offset(, 1).Top + 5, 90, 20)
    ShpShape.TextFrame.Characters.Text = “View”
    ShpShape.TextFrame.HorizontalAlignment = xlHAlignCenter
    wksHome.Hyperlinks.Add ShpShape, “”, “‘” & rngCell.Value & “‘!A1”, “Click”, “”
    ShpShape.Locked = False
    Next rngCell
    Set ShpShape = wksHome.Shapes.AddShape(msoShapeFrame, wksHome.Range(“B1”).Top, wksHome.Range(“B1”).Left, wksHome.Range(“E1”).Left – wksHome.Range(“B1”).Left, wksHome.Range(“C4”).End(xlDown).Offset(2).Top – wksHome.Range(“B1”).Top)
    With ShpShape
    .Select
    Selection.ShapeRange.Adjustments.Item(1) = 0.06063
    Selection.ShapeRange.Adjustments.Item(1) = 0.05143
    .Top = wksHome.Range(“B2”).Top
    .Left = wksHome.Range(“B2”).Left
    End With

    End Sub

    1. thanks for comment. suggestion are most welcome 🙂

  4. I didn’t understand the logic behind creating such huge code just to create hyperlink, which can be done by just putting shapes in the right place and creating hyperlinks to different sheets.

  5. you are right, but the question was how instant you are to do this without code.

  6. jomili says:

    I can’t download the example, and I get a syntax error on this line in either the original code or the amended code

    Set ShpShape = wksHome.Shapes.AddShape(msoShapeFrame, wksHome.Range(“B1”).Top, wksHome.Range(“B1”).Left, wksHome.Range(“E1”).Left – wksHome.Range(“B1”).Left, wksHome.Range(“C4”).End(xlDown).Offset(2).Top – wksHome.Range(“B1”).Top)

  7. HI jomili ,you are right there might be syntax error if you copy paste code from there. that’s why i always provide a downloadable file. when you click that link there would be two button showing for download, you should click on small button showing in bottom.

    thanks for reading ,

    1. Jomili says:

      Problem is there is no “Small button showing in bottom”. I have the “Download” button, and I have a link to download the B1 archiver. If I click the “Download” button, I get a new page that’s identical to the page I came from, no download. If I click on the B1, the page is blocked (I’m at work).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s