A Neat Goto Selector

Quite awhile ago, a thread on a forum I used to volunteer at asked how to implement the mouse scroll wheel inside a ListBox displaying the sheet names in the workbook whose purpose is to make selecting a sheet easier. This reminded me of a "Go To Sheet" solution I came up with sometime before that where selecting the sheet to go to is aided by a "match entry" typing feature... it does not provide for mouse wheel scrolling, but the selection mechanism it does use is, well, neat.

The way it works is you start typing text contained within the name of the sheet you want to go to and the displayed list is filtered to show the sheet names containing those (contiguous) characters somewhere within them. At any point in time (normally once the list gets small enough), you can use the mouse to click a visible entry in the list and you will be taken to that sheet, or you can also use the Up/Down Arrow Keys (use the Down Arrow Key to start it off) to navigate to the desired sheet name in the now filtered list and hit the Enter Key to physically select that sheet. The Left Arrow Key can be used to move you back to the edit field if you find you no longer wish to be using the Up/Down Keys to move about in the currently displayed filtered list.

The UserForm is a very simple design (so you should have no problem constructing it)... a TextBox at the top and a ListBox under it. I used the default names for the two controls but renamed the UserForm to GoToSheetSelectorUserForm so that its name would not conflict with any other UserForms I might want to add at a later date. Here is a picture of what mine looks like mid-typing for reference purposes.

Here is the code to use in the UserForm's code module...

Private Sub UserForm_Initialize()
  Dim Obj As Object
  With Me
    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
  End With
  TextBox1.Text = ""
  TextBox1.EnterKeyBehavior = True
  For Each Obj In Sheets
    If Obj.Visible = xlSheetVisible Then ListBox1.AddItem Obj.Name
  Next
  TextBox1.SetFocus
End Sub


Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
                             ByVal Shift As Integer)
  With TextBox1
    If KeyCode = vbKeyLeft Then
      ListBox1.ListIndex = -1
      .SelStart = Len(.Text)
      .SetFocus
    ElseIf KeyCode = vbKeyReturn Then
      If ListBox1.ListCount > 0 Then
        Sheets(ListBox1.Text).Activate
        Unload Me
      End If
    End If
  End With
End Sub


Private Sub ListBox1_MouseUp(ByVal Button As Integer, _
                             ByVal Shift As Integer, _
                             ByVal X As Single, ByVal Y As Single)
  Sheets(ListBox1.List(ListBox1.ListIndex)).Activate
  Unload Me
End Sub


Private Sub TextBox1_Change()
  Dim X As Long
  ListBox1.Clear
  For X = 1 To Sheets.Count
    If InStr(1, Sheets(X).Name, TextBox1.Text, vbTextCompare) Then
      ListBox1.AddItem Sheets(X).Name
    End If
  Next
End Sub


Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
                             ByVal Shift As Integer)
  With ListBox1
    If KeyCode = vbKeyReturn Then
      KeyCode = 0
      If .ListCount = 0 Then
        Exit Sub
      ElseIf .ListCount = 1 Then
        Sheets(.List(0)).Activate
        Unload Me
      Else
        .SetFocus
        .Selected(0) = True
        .ListIndex = 0
      End If
    ElseIf (KeyCode = vbKeyDown Or (KeyCode = vbKeyRight And _
            TextBox1.SelStart = Len(TextBox1.Text))) And .ListCount > 0 Then
      .SetFocus
      .Selected(0) = True
      .ListIndex = 0
    End If
  End With
End Sub
        

Just some items to handle in closing. First, you will need a method to call up this UserForm. What I did is create this macro to do this...

Sub CallGoToSheetSelector()
 GoToSheetSelectorUserForm.Show
End Sub
        

and then I gave that macro the short-cut CTRL+G (where G stands for "goto"). Yes, I know CTRL+G, by default, bring up Excel's built in "Go To" dialog box, but pressing the F5 will also bring up that same Excel "Go To" dialog box, so my confiscating CTRL+G does not materially affect any built-in Excel capabilities. Second, the way I implemented this was to open a blank workbook, create the UserForm and place its code from above into its code module, added a General code module and put the macro immediately above into it, gave the macro the indicated shortcut and then saved the workbook as a macro-enabled Template. That way, whenever I would create a workbook that I knew I would need this functionality it, I could open that Template instead of the Blank workbook Template and everything would be in place and ready to use without having to do anything more.