Using drop down boxes to access different data

DSNake301 · #1 06-24-2014, 03:12 AM

Hi all. Sorry if this is the wrong place to ask (this is my first post haha). I'm wondering if it's possible to use a drop down box that can access different data? Using football as an example I select Swansea then all their players appear and select the player but then I could change Swansea with Man United and access their players? Thanks

jpb103 · #2 06-24-2014, 08:42 AM

I would use a couple of comboboxes for this. I'd name the first one ComboTeam and the second one ComboPlayers. Then you just need to do some coding in the ComboTeam_Change() sub (it executes the code in this section each time a team is selected). The code would consist of a Select case statement something like this:

Code:

Private Sub ComboTeam_Change()
ComboPlayers.Clear
Select Case ComboTeam.Text
    Case "Swansea"
        ComboPlayers.AddItem "SwanseaPlayerName1"
        ComboPlayers.Additem "SwanseaPlayerName2"
        ...
    Case "Man United"
        ComboPlayers.AddItem "ManUnitedPlayerName1"
        ...
    Case Else
        ComboPlayers.AddItem "Please select a valid team"
End Select
End Sub

Alternatively, and instead of manually coding each player name like this, you could put each player list into text files and load them into an array.

DSNake301 · #3 06-24-2014, 09:35 AM

Is there a way I could have the players in an excel/access sheet, so I could make changes more easily?

jpb103 · #4 06-24-2014, 10:16 AM

I don't see why not. Just make sure you include the reference in your project to the Microsoft Excel 12.0 (or whatever) Object Library (Tools->References...). Does each team have the same number of players? If so, you could do a loop, something like:

Code:

Dim i as integer
Dim wkbkTarget as Workbook
Set wkbkTarget = Excel.Workbooks.Open(C:\path\filename)
For i = 1 to 10 '//If there are 10 players//
    ComboPlayers.Additem wkbkTarget.Range.Cell("A", i)
Next i

I don't know about that exact syntax on the wkbkTarget.Range.Cell part (you might have to reference a specific worksheet) so you'll want to mess around with that probably. If each team doesn't have the same number of players you could always just keep a count in one of the cells on the excel sheet, then just retrieve it and put that into a PlayerCount variable (integer or long) at runtime.

Good luck.

macropod · #5 06-24-2014, 04:24 PM

Quote:

Originally Posted by DSNake301

Is there a way I could have the players in an excel/access sheet, so I could make changes more easily?

Is there a reason for using Word at all?

DSNake301 · #6 06-25-2014, 01:00 AM

It's what the format the people I'm making the document are most comfortable with, and jpb each club would vary, some would need 5 others over 50. Cheers.

macropod · #7 06-25-2014, 01:18 AM

OK but, for the most part, the approaches discussed in this thread have all involved Excel in one way or another. Automating an Excel workbook from Word requires a lot of complexity and maintenance overheads (for an end-user) and either linking to an Excel range or embedding an Excel work still means all the maintenance work has to be done in Excel. Furthermore, if you use an Excel worksheet embedded in the Word document, using the content requires the users to actually use Excel - the document simply becomes a portal into Excel.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
excel drop down boxes	kaschlegel	Excel	1	07-22-2013 10:23 PM
Changing the placeholder text for drop down boxes	DeadBatteries	Word	1	08-24-2012 09:09 AM
Data Validation drop down	rkalapura	Excel	1	05-27-2012 02:23 AM
Dynamically creating new drop down boxes.	dmncs	Word VBA	1	05-06-2012 09:29 PM
Drop Down Boxes	Thomas Farmery	Word	7	01-05-2011 01:58 AM