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 (http://www.excelfox.com/forum/f2/vba-to-scroll-through-listbox-using-mouse-scroll-wheel-1827/)) 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.
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.
DESCRIPTION
A recent thread started in this forum (VBA To Scroll Through ListBox Using Mouse Scroll Wheel (http://www.excelfox.com/forum/f2/vba-to-scroll-through-listbox-using-mouse-scroll-wheel-1827/)) 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.
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.