Selecting a sheet from a drop down box

[Caveman]

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.

 

[Leith Ross]

Hello Caveman,

Yes, you can. You will need a to add a Forms Toolbar ComboBox (Dro
Down) on your "master" worksheet. Select a column to put then player
names in. For this example I'll use column "A". The First player wil
be in cell "A1" and the last player in cell "A26".

1) Right click on your ComboBox. Select "Format Control..." from th
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 mov
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 th
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'l
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 popu
menu. Find "SelectWorksheet" in the list and left it and then left clic
"OK".
Now when you select a name from the list that sheet will be selected.

Sincerely,
Leith Ros

 

[JMay]

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.

 

[Guest]

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

 

[Guest]

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

 

[Debra Dalgleish]

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

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

:

 

[Guest]

nice, thank you =)

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