Sheets Navigator Tool in Excel VBA

You all have that one workbook or spreadsheet, which have definitely more than 50 sheets. Which is very boring to scroll sometimes when you want to work with any random sheets more often.

There are many ways we can perform navigation through sheets. We can create the shapes to navigate there but hardly, we can only do that for a few sheets. Not for every sheet. So, here I'm coming with a permanent and handsome solution.

My Sheet Navigator

How it looks?

How it Works?

it's just initiated with pressing "Ctrl+k"

(I have selected "k" as a shortcut key.)

Select the Sheet you want to navigate to, and double-click it. Navigator takes you to that sheet and userform will be closed automatically.

Isn't easy?

Let's develop this Awesome Tool. 

Step 1: Goto the Developer Tab and click on the Record Macro. You will be asked to name the new macro which you gonna record. Now, don't require to name this macro but if you want then do it. Now, most important is that to assign a macro with the Shortcut key. As I have said, in my case I have chosen the letter "k" and you choose what you want. but remember, if you choose the active shortcut key, after this process, that earlier action will be overlapped by the current macro activities

So save the macro and goto the Developer Tab again and Stop the Recording. Now in VB Editor, you will see this module. 

Step 2: Design the Userform. 

You just need one list box! Insert the Userform and pick the list box from the toolbox and set it just like what you can see in the image. 

Step 3: Coding! 

This tool is running totally on two Events. 

1. UserForm_Initialize event 

2. Listbox ListBox_DblClick Event

Here is the Code for UserForm_Initialize event 

Private Sub UserForm_Initialize()

Application.ScreenUpdating = False

    

    Dim idp As Variant

    idp = ThisWorkBook.Name

    

    Dim Machine As Workbook

    Set Machine = Excel.Workbooks(idp)

    

    Dim sh As Worksheet

    

    For Each AllSH In Machine.Sheets

        Userfrom1.ListBox1.AddItem (sh.Name)

    Next

    

Application.ScreenUpdating = True

End Sub

Here is the Code for Listbox ListBox_DblClick Event 

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Application.ScreenUpdating = False

    

    Dim idp As Variant

    idp = THEIDP.Name

    

    Dim Machine As Workbook

    Set Machine = Excel.Workbooks(idp)

    

    With UserForm1.ListBox1

        Machine.Worksheets(.List(.ListIndex)).Activate

    End With

    

    Unload UserForm1

Application.ScreenUpdating = True

End Sub

There you go...save it and run it! EasyPeasy!!!!

Thank for trying this. 

Regards,

Kamal Bharakhda