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 (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

[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.

"Caveman" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Florida User]

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
>
>

[Bonbon]

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]

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

[Bonbon]

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
>
>