A Neat "Go To Sheet" Selector

**Rick Rothstein** · 05-30-2014, 10:38 PM

Do not be put off by the size of this article... the bulk of it is a step-by-step description of how to use the "Go To Sheet" selector... installation is a snap consisting of saving the files I have attached and then importing two of them into the Excel VBA project of the workbook you want to add it to... that is it, nothing else to do to activate it. I have highlighted in green the only text below that you have to read... the rest of the text is explanatory in nature.

DESCRIPTION

A recent thread started in this forum (VBA To Scroll Through ListBox Using Mouse Scroll Wheel) 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 a while ago 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 any part of the name of the sheet you want and the displayed list is filtered to show only sheet names containing the text you typed up to that point. 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 Arror 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.

DOWNLOADING THE FILES

There are three files that you can import into any workbook to give that workbook the abovementioned functionality. The .frm file along with its companion .frx file is the UserForm plus all its attendant code... they are actually all you need if you are willing to write your own code to show the UserForm. The second file, the .bas one, is a macro that, if imported, provides the show code required to show the UserForm... it can be run by the normal means of pressing ALT+F8 or you can use its built-in short-cut keystroke to run it... that keystroke is CTRL+G. 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.

Okay, that's it. I have Zipped the three files together into one file along with an Excel file you can use for demonstation purposes for downloading, so you will have to Unzip them on your computer once you have finished downloading them. While I would suggest placing the files in their own folder, you can split the .bas file into a separate directory (in case you have a folder devoted exclusively to macros), but the .frm and .frx files must keep be kept together in the same folder at all times. The Excel .xlsx file is for you to practice importing on and, afterward, demonstrating how to use the "Go To Sheet" UserForm, so it can be deleted once you have finished with it.

The Excel file has no data, just eight tabs with names I'll use to demonstrate the UserForm's functionality. So, assuming you have downloaded the files, open up the Excel file. Press ALT+F11 to go into the VB editor, then click "File/Import File..." from its menu bar. Now navigate to the folder you saved the files in and select the file named GoToSheetSelectorUserForm.frm and Open it. Now repeat the process and import the file named GoToSheetSelectorModule.bas (unfortunately, you must to the previous in two separate steps as the "Import File" does not permit multi-file selection for importing). Note that while you downloaded a file with a .frx extension, you don't do anything with it... the .frm file uses it to build the UserForm display.

TUTORIAL

Okay, now that the files are imported, go back to any worksheet and press CTRL+G... the UserForm should appear with all the visible sheets listed and the text cursor in the edit field. First type the letters "ti" (without the quotes, of course, and letter case does not matter)... notice the list filters down to two items. Next, press the Backspace Key, then type the letter "e" (yes, you can fully edit in the text you are typing)... notice the list is now showing six of the eight sheet names. Next, type the letter "a" and note the list is now only five names long. Next, type the letter "k" and watch the list filter down to the two files that start with the letters "teak". Next press the letter "w" to filter down to one sheet name... press the Enter Key to select it. I mentioned it earlier, but I mention it again, you do not have to start with the first letter of the sheet name you want to find... you can start with any letter contained in the sheet name. So, for my test file, if you type an "a" as the first letter, you will get a list of 5 sheet names all containing an "a" somewhere in their name. Next type a "k" and the list will dwindle down to two names.

Okay, now press CTRL+G to bring up the UserForm again, and type the letters "tea" into the edit field, then use the mouse to click on one of the sheet names in the list... doing so will immediately take you to that sheet. You can filter the list down as much as you need and at any stage, use the mouse to click an sheet name an be taken to that sheet. Now, press CTRL+G one last time to bring up the UserForm again, and then type in the letters "tea" into the edit field, but this time, follow that by pressing the Down Arrow Key... notice you are now scrolling inside the list of sheet names directly... you can use the Up and Down Arrow Keys to bring the highlight to the sheet name you want and press the Enter Key to select that name BUT FIRST before you do that, there is one more feature I want you to see. Suppose you had pressed the Down Arrow Key by mistake or after you realized you filtered the list incorrectly, simply press the Left Arrow Key and you will be returned to the edit field where you can change the typed-in letters as needed. Okay, now you can press the Down Arrow Key, navigate to the sheet name you want and press the Enter Key to see how that takes you directly to that sheet.

IN CLOSING

Okay, that's it... I am pretty sure I have shown you all the ways you can use this selector control... hopefully some of you will find this a useful addition to your workbooks, especially those with lots of worksheets in them. So... enjoy!

Oh, one more thing which you may want to consider. Open a blank workbook, import the files included with this thread (but not the .xlsx file though) and then save the workbook as an Excel Macro Enabled Template (.xltm); then, when you want to open a brand-new workbook with the GoTo Selector functionality enabled, select the "GoTo Selector" template instead of the "Blank Workbook" template.

Code:

Dim SheetNames() As String

Private Sub UserForm_Initialize()
  Dim Obj As Object
  TextBox1.Text = ""
  TextBox1.EnterKeyBehavior = True
  ReDim SheetNames(0 To Sheets.Count - 1)
  For Each Obj In Sheets
    SheetNames(Obj.Index - 1) = Obj.Name
    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
  Dim Pages() As String
  Pages = Filter(SheetNames, TextBox1.Text, True, vbTextCompare)
  If Len(TextBox1.Text) Then
    If UBound(Pages) > -1 Then
      With ListBox1
        .Clear
        For X = 0 To UBound(Pages)
          .AddItem Mid$(Pages(X), 1)
        Next
      End With
    Else
      ListBox1.Clear
    End If
  Else
    ListBox1.Clear
    For X = 0 To UBound(SheetNames)
      ListBox1.AddItem Mid$(SheetNames(X), 2)
    Next
  End If
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

For those of you who decide to try out my "Go To Sheet" selector, let me know what you think of it... all comments are welcome, both good and bad.

**Rick Rothstein** · 06-02-2014, 12:09 AM

My hope is that for the many of you who have viewed this article to date but have not physically tried the attached code, this message will trigger an alert to you. I have added a note to the beginning of the article (in red text) which might address whatever it is that stopped you from trying the "Go To Sheet" selector out... so, please read that and reconsider downloading the files for testing purposes at least.

https://www.youtube.com/@alanelston2330/featured

**Rick Rothstein** · 06-03-2014, 01:05 AM

In the thread that this one was based on (VBA To Scroll Through ListBox Using Mouse Scroll Wheel), MrBlackd raised an excellent point about font size and modern monitors with higher resolutions than I use. It seems that on those monitors, my UserForm comes out somewhat smallish. MrBlackd suggest using the font and font size he uses and I have done so; he also made an excellent suggestion as to making sure the form appears on the same screen (important for multi-monitor setups) and I have adopted the code he posted to do this as well. So thanks go out to MrBlackd for his suggestions! I have modified the code attached to Message #1 as indicated above. If have you already have downloaded my original files earlier, you should replace them with the new files now contained in the attachment to Message #1.

**snb** · 06-03-2014, 09:17 PM

**Rick Rothstein** · 06-03-2014, 10:29 PM

Originally Posted by snb

To add to the 'neat' requirement see the attachment

That is another way to approach it, for sure, but personally, I do not like it. The automatic selection of the item when it is the last remaining item is kind of disconcerting to me, especially if I (unknowingly) mistyped the letter that made the entry unique and the dialog box disappears... kind of leaves me wondering. Of course, maintaining the last entry on calling up the control the way you do helps mitigate this, but then that feature forces you to delete the entry in the edit field the next time you call the control up to look for a sheet with a completely different spelling. I see you handled that by having the Left Arrow Key erase the entry, but that changes the functionality I implemented allowing you to hit the Left Arrow Key and continue editing the text in the edit field (in case you mistakenly entered the ListBox and want to return to the edit field). You also killed off the Enter Key as a selector... if I am using the arrow keys to move up or down in the ListBox, the natural thing to do is hit the Enter Key when I have the item I want selected because my hands are on the keyboard. As I said, the above are my own personal feeling, but I do appreciate your posting your version of the "Go To Sheet" selector as I am sure it will appeal to others who read this thread, so it is good that you made it available.

**MrBlackd** · 06-04-2014, 03:30 AM

To call the form:

Code:

Sub CallGoToSheetSelector()
  GoToSheetSelectorUserForm.Show
End Sub

To center form and to focus in textbox so as to start typing immediately:

Code:

Private Sub UserForm_Activate()
    
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
   
Me.TextBox1.SetFocus
   
End Sub

Friendly greetings.

**Rick Rothstein** · 06-04-2014, 04:48 AM

Originally Posted by MrBlackd

To call the form:

Code:

Sub CallGoToSheetSelector()
  GoToSheetSelectorUserForm.Show
End Sub

To center form and to focus in textbox so as to start typing immediately:

Code:

Private Sub UserForm_Activate()
    
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
   
Me.TextBox1.SetFocus
   
End Sub

Friendly greetings.

Who are you directing your comments to?

**MrBlackd** · 06-04-2014, 12:23 PM

In the version that I had downloaded setfocus to textbox was not there, probably you have included it in the updated version but I hadn't seen it, that is why I posted...
My mistake, please delete my post...

**snb** · 06-06-2014, 02:26 AM

It's simple to introduce the 'enter' selection method.
I'ts also very easy to comment out the 'automatic selection' option:

Code:

Private Sub UserForm_Initialize()
    Left = Application.Left + (0.5 * Application.Width) - (0.5 * Width)
    Top = Application.Top + (0.5 * Application.Height) - (0.5 * Height)
  
    For Each sh In Sheets
      If sh.Visible Then ListBox1.Tag = ListBox1.Tag & "_" & sh.Name
    Next
End Sub
Private Sub UserForm_Activate()
    ListBox1.List = Split(Mid(ListBox1.Tag, 2), "_")
End Sub

Private Sub TextBox1_Change()
  ListBox1.List = Filter(Split(Mid(ListBox1.Tag, 2), "_"), TextBox1.Text)
  
  If ListBox1.ListCount = 1 Then M_select
End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 40 And ListBox1.ListCount > 1 Then
        ListBox1.SetFocus
        ListBox1.ListIndex = 0
    End If
End Sub

Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 37 Then
       TextBox1.SetFocus
       ListBox1.ListIndex = -1
    ElseIf KeyCode = 13 Then
        M_select
    End If
End Sub
Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  M_select
End Sub

Sub M_select()
    Sheets(ListBox1.List(ListBox1.ListIndex)).Activate
    Hide
End Sub

**jomili** · 06-11-2014, 11:48 PM

SNB,
I always like your code, but this current version has a hiccup, and I don't easily see how to fix it. Going with your sample, if instead of Selecting "Teak Color" I key it in in the search box, I get a debug error when I key in the space between the words. Error is Run-Time 381, could not get the List property, Invalid property array index.