Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I find a worksheet tab in a spreadsheet with multiple tabs?
I have a spreadsheet that contains a worksheet for each employee we have.
There are approximately 111 employees. I have named each tab by the employee last name. I have arranged the worksheets/tabs in alphabetical order. When I need to find a certain employees tab, I currently have to scan over each tab to find the employee that I need. I've looked at the "go to" feature but it does not allow me to name the tab to go to. I also can't locate it by using the "find" feature. Is there any way that I can type the employee last name so that it will automatically take me to that tab? The way I am doing it takes me forever. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I find a worksheet tab in a spreadsheet with multiple tabs?
I would create a TOC tab. Column A in the TOC tab will have hyperlinks to
each of the other tabs. The columns would look like a simple list of employees in alphabetical order. A single click would take you to the correct tab. -- Gary''s Student - gsnu200856 "Mickey Mouse Tiger" wrote: I have a spreadsheet that contains a worksheet for each employee we have. There are approximately 111 employees. I have named each tab by the employee last name. I have arranged the worksheets/tabs in alphabetical order. When I need to find a certain employees tab, I currently have to scan over each tab to find the employee that I need. I've looked at the "go to" feature but it does not allow me to name the tab to go to. I also can't locate it by using the "find" feature. Is there any way that I can type the employee last name so that it will automatically take me to that tab? The way I am doing it takes me forever. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I find a worksheet tab in a spreadsheet with multiple tabs?
If you rightclick on the VCR like controls (to the left of the worksheet tabs),
you'll see a list of worksheets in that workbook. Other options: Maybe you could use David McRitchie's Build Table of Contents to get the list and the hyperlinks all at once: http://www.mvps.org/dmcritchie/excel/buildtoc.htm Another option if you want a more generic solution: http://contextures.com/xlToolbar01.html (From Debra Dalgleish's site.) It builds a toolbar that you can use with any workbook to navigate to any worksheet. There's a link on that site for an xl2007 version for the ribbon (from Ron de Bruin). If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Mickey Mouse Tiger wrote: I have a spreadsheet that contains a worksheet for each employee we have. There are approximately 111 employees. I have named each tab by the employee last name. I have arranged the worksheets/tabs in alphabetical order. When I need to find a certain employees tab, I currently have to scan over each tab to find the employee that I need. I've looked at the "go to" feature but it does not allow me to name the tab to go to. I also can't locate it by using the "find" feature. Is there any way that I can type the employee last name so that it will automatically take me to that tab? The way I am doing it takes me forever. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I find a worksheet tab in a spreadsheet with multiple tabs?
Here is a previous post of mine which describes how to create a "goto"
UserForm with type-ahead capabilities that you may find useful... Okay, here is something I think will meet your needs (don't be put off by the its length, it is really worth the effort)... its a UserForm which lets you type partial entries, pick from a list and takes you to the indicated sheet. I'm going to assume you do not know how to implement a UserForm and talk you through the whole process. Go to the Visual Basic editor (press Alt+F11 from any worksheet). Once there, click Insert/UserForm form the menu bar. You will see a blank form and a window with the caption Toolbox. The Toolbox has controls (TextBox, ListBox, etc.) on it that you will be placing on the blank UserForm (note.. the Toolbox is displayed only when the UserForm has focus). Before we put any controls on the UserForm, let's rename it. On the left side of the VB editor should be a window with the caption "Properties - UserForm1" (if you don't see this, press F4). The first field of the Properties window is labeled (Name) and next to it is the word UserForm1... click the word UserForm1 and type GoToSheetSelector in its place. Next, look down and find the Height and Width properties and set them to these values... Height = 230 Width = 165 These, and other properties for the controls we will be putting on the UserForm, are initial settings to get you started... you will be able to modify them later to suit your own tastes. The rest of the default values for the UserForm should be fine for now. Okay, let's set the UserForm up. There will only be two controls on the UserForm... a TextBox and a ListBox. First, the TextBox. Give the UserForm focus (to make the Toolbox visible) and click on the TextBox icon in the Toolbox (it is the lower case letters "ab" followed by a vertical line) and then click/drag on the UserForm to place it. You can use the resize handles to make the TextBox whatever size you want and you can click/drag it to any position you want, but we will use the Properties window to set them to exact values. There are only seven properties I want you to set initially. In the Properties window, find and set the following values... Height = 18 Left = 10 MultiLine = True Top = 10 Width = 140 WordWrap = False The seventh property is actually a dialog box for an object where you will set 3 properties of the object. Click on the field to the right of the Font property and you will see a little button with 3 dots in it... click that button. On the dialog box that appears, set these properties.... Font = Arial Font Style = Bold Size = 10 Okay, that takes care of the TextBox. Next, let's add the ListBox. Click the ListBox icon in the Toolbox (it is a rectangle with 3 horizontal lines on the left and what are supposed to be up/down arrows on the right... if you hover the mouse over the controls, a tooltip will appear with the control's name... you are looking for ListBox) and then click/drag on the UserForm to place it. Set the following properties for it in the Properties window... Height = 165 Left = 10 Top = 38 Width = 140 Okay, that should take care of setting up the UserForm; now let's install the code. Press F7 to bring up the code window for the UserForm (or simply double click anywhere on the UserForm or one of its controls). Delete anything you see in the code window that appears and copy/paste **all** of the marked off code that follows my signature into this code window. Okay, that takes care of the UserForm itself, now we just need a way to call it up. Still in the VB editor, click Insert/Module from the menu bar. Another code window will appear; copy/paste this into it... Sub CallGoToSheetSelector() GoToSheetSelector.Show End Sub Okay, that is pretty much it... the UserForm is usable right now. From any sheet in your workbook, press Alt+F8, select CallGoToSheetSelector from the list and click Run (we can do this differently and I'll explain how in a moment). The UserForm will be displayed showing all sheets in your workbook in the ListBox and the cursor will be in the TextBox. Here is how the UserForm works. Start to type in the name of a sheet in your workbook and the ListBox will show only sheet names starting with that text. You can continue to type until only one name is left in the ListBox (you might not have to type the full sheet name to completion for this to happen) and then press the Return key to go to that sheet. But you don't have to keep typing until there is only one sheet name left in the ListBox... at any time, you can press either the down or right arrow and you will find yourself in the ListBox itself where you can continue to use the arrow keys to place the highlight on the sheet name you want, then press Return to go to that sheet. If you find yourself in the TextBox by mistake, just press the left arrow key to put yourself back into the TextBox. You can edit the text in the TextBox and the ListBox will display the sheet names corresponding to the type in text. If you type text that is not the starting text of a sheet name, the ListBox will not display anything (if you mistype a letter so this occurs, just delete the mistyped letter and the ListBox will adjust accordingly). Oh, and you can also just click an entry in the ListBox with your mouse and that will take you to the clicked on sheet name. Okay, now about alternate methods of activating the UserForm (besides always using Alt+F8/Select/Run). Go to any worksheet and press Alt+F8, select CallGoToSheetSelector from the list and then click the Options button. This will bring up a dialog box which lets you assign a shortcut key to your macro. Type a lower case "g" in the indicated field, then click OK and close the dialog box. Now, from any sheet in your workbook, press Ctrl+g and your UserForm will appear, ready to use. I think that is everything. If you have any questions, feel free to post back. -- Rick (MVP - Excel) ' *************** START OF CODE *************** Option Explicit 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 ' *************** END OF CODE *************** "Mickey Mouse Tiger" wrote in message ... I have a spreadsheet that contains a worksheet for each employee we have. There are approximately 111 employees. I have named each tab by the employee last name. I have arranged the worksheets/tabs in alphabetical order. When I need to find a certain employees tab, I currently have to scan over each tab to find the employee that I need. I've looked at the "go to" feature but it does not allow me to name the tab to go to. I also can't locate it by using the "find" feature. Is there any way that I can type the employee last name so that it will automatically take me to that tab? The way I am doing it takes me forever. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find my worksheet tabs? | Excel Worksheet Functions | |||
Using the "Find" option across multiple tabs in one worksheet | Excel Discussion (Misc queries) | |||
link multiple tabs in new worksheet | Excel Discussion (Misc queries) | |||
How can I find/unhide hidden excel sheet tabs on a worksheet? | Excel Worksheet Functions | |||
How do I stack worksheet tabs on an excel spreadsheet? | Excel Worksheet Functions |