Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop down list
Dear sir, with best regards : I have a workbook with around 25+ sheets on it and I was wondering if its possible if I could put these sheets in a drop down list in say cell A1( sheet 1) so it would be easier to navigate and jumping to the sheets. Is this possible with or without VBA code if so how would I go about doing it? Thank you for your help and time in adavance... -- tofimoon3 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop down list
Hi
Did you know that you already have a dropdown list for your Tabs (Sheets). At the bottom to the left of the sheet tabs, right click on the arrows and you will get the listing of all your tabs. HTH John "tofimoon3" wrote in message ... Dear sir, with best regards : I have a workbook with around 25+ sheets on it and I was wondering if its possible if I could put these sheets in a drop down list in say cell A1( sheet 1) so it would be easier to navigate and jumping to the sheets. Is this possible with or without VBA code if so how would I go about doing it? Thank you for your help and time in adavance... -- tofimoon3 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop down list
David McRitchie has some code that creates a table of contents (using
hyperlinks): 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 to an xl2007/ribbon version (by Ron de Bruin) on Debra's site. ======= If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) tofimoon3 wrote: Dear sir, with best regards : I have a workbook with around 25+ sheets on it and I was wondering if its possible if I could put these sheets in a drop down list in say cell A1( sheet 1) so it would be easier to navigate and jumping to the sheets. Is this possible with or without VBA code if so how would I go about doing it? Thank you for your help and time in adavance... -- tofimoon3 -- Dave Peterson |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop down list
Hi,
You can try this. Ctrl+k in Link to (LHS) This document|and select the sheet in the current workbook|Ok the link will get pasted in the cell you had selected. -- _______________________ Click "Yes" if it helps ________ Thanks Suleman Peerzade "tofimoon3" wrote: Dear sir, with best regards : I have a workbook with around 25+ sheets on it and I was wondering if its possible if I could put these sheets in a drop down list in say cell A1( sheet 1) so it would be easier to navigate and jumping to the sheets. Is this possible with or without VBA code if so how would I go about doing it? Thank you for your help and time in adavance... -- tofimoon3 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Drop down list
Here is something I have posted previously which you might 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. ' *************** 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 *************** -- Rick (MVP - Excel) "tofimoon3" wrote in message ... Dear sir, with best regards : I have a workbook with around 25+ sheets on it and I was wondering if its possible if I could put these sheets in a drop down list in say cell A1( sheet 1) so it would be easier to navigate and jumping to the sheets. Is this possible with or without VBA code if so how would I go about doing it? Thank you for your help and time in adavance... -- tofimoon3 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop down list dependant on previous drop down list | Excel Discussion (Misc queries) | |||
Drop down lists that auto create and then filter the next drop down list | Excel Worksheet Functions | |||
Drop Down List choice selecting another drop down list | Excel Worksheet Functions | |||
how do I link a drop down list entry to a new drop down cell? | Excel Discussion (Misc queries) | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) |