Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: A Neat "Go To Sheet" Selector

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13

    A Neat "Go To Sheet" Selector

    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.
    Attached Files Attached Files
    Last edited by Rick Rothstein; 05-30-2022 at 12:56 AM.

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    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
    Last edited by DocAElstein; 09-13-2023 at 11:15 AM.

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    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.
    Last edited by Rick Rothstein; 06-03-2014 at 01:11 AM.

  4. #4
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    To add to the 'neat' requirement see the attachment



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=oVb1RfcSHLM&lc=UgwTq-jZlZLnLQ5VB8Z4AaABAg.9Hroz-OyWog9tYjSMc1qjA
    https://www.youtube.com/watch?v=0pbsf6sox34&lc=Ugxp9JFvvejnqA68W1t4AaABAg
    https://www.youtube.com/watch?v=kfQC-sQxMcw&lc=UgyCxQWypNIhG2nUn794AaABAg.9q1p6q7ah839t UQl_92mvg
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg. 9iEktVkTAHk9iF9_pdshr6
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg. 9iDVgy6wzct9iFBxma9zXI
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg. 9iDQN7TORHv9iFGQQ5z_3f
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg. 9iDLC2uEPRW9iFGvgk11nH
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg. 9iH3wvUZj3n9iHnpOxOeXa
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg. 9iGReNGzP4v9iHoeaCpTG8
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Attached Files Attached Files
    Last edited by DocAElstein; 09-13-2023 at 11:02 AM.

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by snb View Post
    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.

  6. #6
    Member
    Join Date
    Jul 2013
    Posts
    40
    Rep Power
    0

    Another tiny sugetstion ...

    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.
    Keep in mind all vba I know has been googled...

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by MrBlackd View Post
    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?

  8. #8
    Member
    Join Date
    Jul 2013
    Posts
    40
    Rep Power
    0
    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...
    Keep in mind all vba I know has been googled...

  9. #9
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    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
    Attached Files Attached Files
    Last edited by snb; 06-06-2014 at 02:18 PM.

  10. #10
    Junior Member
    Join Date
    Jul 2011
    Posts
    19
    Rep Power
    0
    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.

Similar Threads

  1. VBA Versions of my "Get Field" and "Get Reverse Field" formulas
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 4
    Last Post: 06-02-2017, 06:15 PM
  2. Reversing a "First Middle Last" Name to "Last, First Middle" Name Format
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 5
    Last Post: 01-06-2014, 10:04 PM
  3. Replies: 4
    Last Post: 09-09-2013, 05:13 PM
  4. Replies: 5
    Last Post: 04-18-2013, 02:30 AM
  5. Ordinal Suffix (i.e., "st", "nd", "rd" and "th")
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 0
    Last Post: 03-20-2012, 03:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •