Wednesday, August 1, 2018

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 

IsValidPasswordString Function

'Following function will verify if the password string contains following characters or not? Rem : List of Characters Group - ASCII Rem ...