Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting a sheet from a drop down box
I have a list of cricket players names (26) each has thier own spreadsheet with all thier info on it (batting averages, etc.etc) can i use a drop down box with thier names in to open the sheet with all thier info in. -- Caveman ------------------------------------------------------------------------ Caveman's Profile: http://www.excelforum.com/member.php...o&userid=27546 View this thread: http://www.excelforum.com/showthread...hreadid=505120 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting a sheet from a drop down box
Hello Caveman, Yes, you can. You will need a to add a Forms Toolbar ComboBox (Drop Down) on your "master" worksheet. Select a column to put then players names in. For this example I'll use column "A". The First player will be in cell "A1" and the last player in cell "A26". 1) Right click on your ComboBox. Select "Format Control..." from the popup menu. Next, click the tab marked "Control" in the dialog box. 2) Click in the box marked "Input Range". 3) Left Click on cell "A1" and hold the mouse button down as you move the mouse to cell "A26". 4) Release the left mouse button. 5) In the "Input Range" box you should see $A$1:$A$26. 6) Click "OK". Now we need a macro to select the sheet using the name shown in the ComboBox. Copy this macro code to Clipboard using CTRL + C . Sub SelectWorksheet() Dim cboName As String Dim WksName As String cboName = Application.Caller With ActiveSheet.Shapes(cboName).ControlFormat WksName = .List(.ListIndex) End With If WksName = "" Then Exit Sub Else Worksheets(WksName).Activate End If End Sub 1) Press the ALT key and hold it and then press the F11 key. I'll abbreviate any key sequence like this one as Key1+Key2. 2) Press ALT+I to drop down the Insert Menu. 3) Press M to insert a module into your Workbook. 4) Press CTRL+V to paste the code into the module. 5) Press CTRL+S to save the macro. 6) Press ALT+Q to close the Editor and return to Excel. Right Click on the ComboBox and select "Assign Macro..." from the popup menu. Find "SelectWorksheet" in the list and left it and then left click "OK". Now when you select a name from the list that sheet will be selected. Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=505120 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting a sheet from a drop down box
FWIW,,,
As a simple alternative you can (without code and controls) right-click on any of the 4 VCR buttons (at bottom-left) and as you do a listing of all your sheets will appear, you can select from there. "Caveman" wrote in message ... I have a list of cricket players names (26) each has thier own spreadsheet with all thier info on it (batting averages, etc.etc) can i use a drop down box with thier names in to open the sheet with all thier info in. -- Caveman ------------------------------------------------------------------------ Caveman's Profile: http://www.excelforum.com/member.php...o&userid=27546 View this thread: http://www.excelforum.com/showthread...hreadid=505120 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting a sheet from a drop down box
An easy solution would be add a worksheet that is the master list of players
and then create a hyperlink to each worksheet. Insert Hyperlink Place in this Document Select the worksheet Change the Test To Display to the player's name On each player's worksheet you can add a hyperlink to return to the main sheet with your list of players. When you create this link once you can copy it to each worksheet. Hope this helps "Caveman" wrote: I have a list of cricket players names (26) each has thier own spreadsheet with all thier info on it (batting averages, etc.etc) can i use a drop down box with thier names in to open the sheet with all thier info in. -- Caveman ------------------------------------------------------------------------ Caveman's Profile: http://www.excelforum.com/member.php...o&userid=27546 View this thread: http://www.excelforum.com/showthread...hreadid=505120 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting a sheet from a drop down box
sorry, this is really good. However what if, there is an update in names?
Because i want to do something like this too, but im using Dates, and i will keep adding/updating, so is there any way for Excel to auto update the list of Dates in the combo box + opening the chosen worksheets??? Thx in advance, Bonbon "Florida User" wrote: An easy solution would be add a worksheet that is the master list of players and then create a hyperlink to each worksheet. Insert Hyperlink Place in this Document Select the worksheet Change the Test To Display to the player's name On each player's worksheet you can add a hyperlink to return to the main sheet with your list of players. When you create this link once you can copy it to each worksheet. Hope this helps "Caveman" wrote: I have a list of cricket players names (26) each has thier own spreadsheet with all thier info on it (batting averages, etc.etc) can i use a drop down box with thier names in to open the sheet with all thier info in. -- Caveman ------------------------------------------------------------------------ Caveman's Profile: http://www.excelforum.com/member.php...o&userid=27546 View this thread: http://www.excelforum.com/showthread...hreadid=505120 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting a sheet from a drop down box
There are instructions and a download here for creating a toolbar that
lists the sheets in a workbook, and activates the selected sheet: http://www.contextures.com/xlToolbar01.html Bonbon wrote: sorry, this is really good. However what if, there is an update in names? Because i want to do something like this too, but im using Dates, and i will keep adding/updating, so is there any way for Excel to auto update the list of Dates in the combo box + opening the chosen worksheets??? Thx in advance, Bonbon "Florida User" wrote: An easy solution would be add a worksheet that is the master list of players and then create a hyperlink to each worksheet. Insert Hyperlink Place in this Document Select the worksheet Change the Test To Display to the player's name On each player's worksheet you can add a hyperlink to return to the main sheet with your list of players. When you create this link once you can copy it to each worksheet. Hope this helps "Caveman" wrote: I have a list of cricket players names (26) each has thier own spreadsheet with all thier info on it (batting averages, etc.etc) can i use a drop down box with thier names in to open the sheet with all thier info in. -- Caveman ------------------------------------------------------------------------ Caveman's Profile: http://www.excelforum.com/member.php...o&userid=27546 View this thread: http://www.excelforum.com/showthread...hreadid=505120 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting a sheet from a drop down box
nice, thank you =)
"Debra Dalgleish" wrote: There are instructions and a download here for creating a toolbar that lists the sheets in a workbook, and activates the selected sheet: http://www.contextures.com/xlToolbar01.html Bonbon wrote: sorry, this is really good. However what if, there is an update in names? Because i want to do something like this too, but im using Dates, and i will keep adding/updating, so is there any way for Excel to auto update the list of Dates in the combo box + opening the chosen worksheets??? Thx in advance, Bonbon "Florida User" wrote: An easy solution would be add a worksheet that is the master list of players and then create a hyperlink to each worksheet. Insert Hyperlink Place in this Document Select the worksheet Change the Test To Display to the player's name On each player's worksheet you can add a hyperlink to return to the main sheet with your list of players. When you create this link once you can copy it to each worksheet. Hope this helps "Caveman" wrote: I have a list of cricket players names (26) each has thier own spreadsheet with all thier info on it (batting averages, etc.etc) can i use a drop down box with thier names in to open the sheet with all thier info in. -- Caveman ------------------------------------------------------------------------ Caveman's Profile: http://www.excelforum.com/member.php...o&userid=27546 View this thread: http://www.excelforum.com/showthread...hreadid=505120 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get a row consecutive from a sheet to another | Excel Worksheet Functions | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) | |||
Clicking Cell Link Changes Cell on Another Sheet | Excel Discussion (Misc queries) | |||
Selecting from a Validation Drop Down Box aborts macro | Excel Worksheet Functions | |||
Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel |