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.

No alt text provided for this image

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.

要查看或添加评论,请登录

Rick Rothstein的更多文章

  • The SplitTo Function...

    The SplitTo Function...

    ***** EDIT NOTE ***** The original formula in this article had a minor error which has now been corrected. If you…

    9 条评论
  • TEXT Function Array Argument

    TEXT Function Array Argument

    Victor Momoh (MOS,R.Eng) posted an article/video (see link below) which explains that the TEXT function can use an…

    2 条评论
  • Thinking About Using VBA's IsNumeric Function? Read this first.

    Thinking About Using VBA's IsNumeric Function? Read this first.

    I have posted the following many times over the last 15 years or so in responding to old newsgroup and current forum…

    2 条评论
  • Get Random Number Of Unique Items From A Range Or An Array

    Get Random Number Of Unique Items From A Range Or An Array

    A few weeks ago, I posted an article showing how to get one or more non-repeating random numbers between a specified…

    17 条评论
  • Color Constants For Use In VBA Code

    Color Constants For Use In VBA Code

    There are eight pre-defined color constants in VBA which can be used directly without knowing their underlying numeric…

    3 条评论
  • Find Text Matching A Specified Pattern

    Find Text Matching A Specified Pattern

    Yesterday (December 12, 2021), I posted a post that contained a function to find text that matched a given pattern…

    5 条评论
  • YES, formulas CAN change values in OTHER cells!

    YES, formulas CAN change values in OTHER cells!

    Okay, one thing we have been told over and over again is that a formula, even a UDF (user defined formula) cannot put a…

    10 条评论

社区洞察

其他会员也浏览了