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

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

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

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

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

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

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