Related drop down boxes in excel 10

[jmj]

I'm wondering if there's any way to do this in an excel sheet.
I need to have 2 drop down boxes on the excel sheet.
Depending on what is chosen in the first box will dictact what choices there are in the 2nd box.
For example if the first box is states and I choose Alaska, the second box would only show cities in Alaska.

I need to do this with a music sheet. If they pick jazz they have certain choices, if they pick choral there are other choices.
Doable?
Thanks in advance for any ideas,
J

 

[DaleWatson123321]

jml,

It’s doable – just a “little lengthy” to explain (properly). I suspect this might be why no one has yet responded to your posting.

I’ve created a working model and based it on your State and Cities example. It’s unfortunate I can’t simply email you the file, but Tek-Tips does not permit the posting of emails due to spamming problems. However, the following, while detailed, will hopefully serve the purpose of providing clarity for ANYONE (regardless of skill level) who might want to create such “interactive” DropDown boxes.

The following is both an explanation and detailed description of what you’ll need to do. In the hope that it’ll be easier for you to follow, I’ll specify the specific cells where I’ve placed the components. I’ll list the components in an order that is required - to match with the formulas – some of which would cause an ERROR if you entered the formulas before creating the range names used in the formulas to refer to the components.

#1. Place the following text into a Text Box that covers D1:N5. (Adjust the row height if necessary to accommodate the following text)

The First DropDown box (used for choosing the State) has VBA code attached. When the user chooses a State, the value in the cell named "choice" is used by the VBA code to re-assign a common range name ("Cities_Current") based on the state chosen. The "Cities_Current" name is then used by the Second DropDown box, to display the cities related to selection from the First DropDown box.

#2. Place the First DropDown box over A7:B8. (For clarity, a “DropDown” box is created from the “Combo Box” icon found on the “Forms” toolbar, as opposed to the “Combo Box” icon found on the “Control Toolbox” toolbar.)

#3. Make the column-width “2” for Column C.

#4. In D7, enter the label “state”. (don’t enter the “ ” – applies to all text to be entered).

#5. In E7, hold down the <Ctrl> key, and hit <F3> (This brings up the Define Name window). Because you’ve entered the label “state” opposite in D7, the name “state” will automatically appear in the Define Name box. Therefore, simply hit <Enter>.

#6. In D8, enter the label “state_name”.

#7. In E8, do the same as described in #5 – i.e. create a range name based on the label in D8). (There will be a formula later for E8).

#8. Place the Second DropDown box over A11:B12.

#9. In D11, enter the label “city”.

#10. In E11, do the same as described in #5 – i.e. create a range name based on the label in D11).

#11. In D12, enter the label “city_name”.

#12. In E12, do the same as described in #5 – i.e. create a range name based on the label in D12). (There will be a formula later for D12).

#13. In G11, enter the label “states”.

#14. In H11, enter “Alaska”. In H12, enter “Florida”. In H13, enter “New York”. (I’m only providing 3 states in this example, but naturally based on specific applications, there will be a need to enter additional items to the list.)

#15. Assign the name “states” to the 3 cells, as follows: Highlight the 3 states. Hold down <Ctrl> and hit <F3>. (The name “states” should appear in the Define Name box.) Hit <Enter>.

#16. In I11 (opposite Alaska), enter the number 1. Opposite Florida, enter 2, and opposite New York, enter 3.

#17. In J11, enter the formula: =H11 …and then copy the formula to J12:J13.

#18. In I10, enter the label: state_names

#19. Assign the name “state_names” to I11:J13, as follows: Highlight the range, <Ctrl> F3>. In this type of situation, the name will not automatically appear in the Define Name window. Therefore type the name “state_names”, and hit <Enter>.

#20. Now Right-Click on the First DropDown box, and choose “Format Control”. Click on the “Control” tab. Opposite “Input range:”, enter: states. Opposite “Cell link”, enter: state. The number of “Drop down lines:” is optional – i.e. it defaults to 8, and you can increase this if you prefer.

#21. Pick a state from the First DropDown box. You’ll notice the value from your choice will be placed into the cell you’ve named “state”.

#22. Copy and Paste the following formula into cell E8 (the cell you’ve named “state_name”).
=VLOOKUP(state,state_names,2,FALSE)
…and you’ll notice the formula looks up the state, based on the choice made from the First DropDown box.

#23. You now need to create the city-lists for the 3 states. This will be done in a similar method as for the “states” and “state_names”.

#23a. In L11, enter: “cities_1”.

#23b. In M11:M15, I’ve entered the labels: Alaska_1, Alaska_2, Alaska_3, Alaska_4, and Alaska_5. The 1 through 5 numbers are simply to represent different city names for this example. If you wish to enter actual names, you naturally have that option. (I’m not sure there are 5 cities in Alaska ?).

#23c. Assign the name “cities_1” to your Alaska cities… Highlight M11:M15. <Ctrl> <F3>. “cities_1” should appear. Hit <Enter>.

#23d. In N11:N15, enter the number 1 through 5.

#23e. In O11, enter the formula: =M11, and then copy it down to M11:M15.

#23f. In N10, enter the label “cities_1_names”.

#23g. Assign the name “cities_1_names” to N11:O15. Highlight N11:O15, <Ctrl> <F3>. Type the name: cities_1_names, and hit <Enter>.

For the next states (Florida and New York), it’ll be similar to the steps used for Alaska, with the only difference being that we’ll use the “auto-fill” method.

#24a. In Q11, enter: “cities_2”. (Remember: don’t enter the quotes for any entries.)

#24b. In R11, enter: “Florida_1”. Copy R11 to R12, and change the 1 to 2.

#24c. In S11, enter 1. In S12, enter 2.

#24d. In T11, enter the formula: =R11, and copy the formula from T11 to T12.

#24e. Now for the auto-fill… Highlight R11:T12. Using the mouse, click on the bottom-right-corner of the highlighted range. (You should get a black cross – i.e. a large plus sign ( + ). Now drag this down to row 20, and release the left-mouse-button. This will give you 10 cities for Florida.

#24f. Assign the range name for “cities_2”. Highlight R11:R20. <Ctrl> <F3>. “cities_2” should appear. Hit <Enter>.

#24g. In S10, enter the label “cities_2_names”.

#24h. Assign the name “cities_2_names” to S11:T20. Highlight S11:T20, <Ctrl> <F3>. Type the name: cities_2_names, and hit <Enter>.

For New York, I’d like you to enter 20 cities – because with auto-fill, it’s easy, and because it’ll allow you to “visualize the effect” – of seeing the various lengths of DropDown lists that are automatically modified.
#25a. In V11, enter: “cities_3”. (Remember: don’t enter the quotes for any entries.)

#25b. In W11, enter: “New York_1”. Copy W11 to W12, and change the 1 to 2.

#25c. In X11, enter 1. In X12, enter 2.

#25d. In Y11, enter the formula: =W11, and copy the formula from Y11 to Y12.

#25e. Now for the auto-fill… Highlight W11:Y12. Using the mouse, click on the bottom-right-corner of the highlighted range. (You should get a black cross – i.e. a large plus sign ( + ). Now drag this down to row 30, and release the left-mouse-button. This will give you 20 cities for New York.

#25f. Assign the range name for “cities_3”. Highlight W11:W30. <Ctrl> <F3>. “cities_3” should appear. Hit <Enter>.

#25g. In X10, enter the label “cities_3_names”.

#25h. Assign the name “cities_3_names” to X11:Y30. Highlight X11:Y30, <Ctrl> <F3>. Type the name: cities_3_names, and hit <Enter>.

#26. One last range name to create. First, in cell H7, enter the label “choice”. In I7, use <Ctrl> <F3>, and <Enter>.

#27. Having created the above range names you’re now ready to enter the formulas that reference the range names. In Cell I7 (the cell named “choice”), copy and paste the following formula:
=CHOOSE(state,"cities_1","cities_2","cities_3")

#28. In E12 (the cell named “city_name”), copy and paste this formula:
=IF(city="","",VLOOKUP(city,INDIRECT(CHOOSE(state,"Cities_1_Names","Cities_2_Names","Cities_3_Names",FALSE)),2))

#29a. You now need to insert the VBA code that will later be assigned to the First DropDown box. Remember… this is also for those not experience with VBA, so please don’t take offence at my detail if you’re a “VBA guru”.

#29b. Hold down <Alt> and hit <F11>. This will bring up the “VBA Editor”. On the left (under “Project – VBA Project), make sure you’re clicked on the file you’re working on (in case there’s more than one file open). Then from the VBA menu, choose: Insert – Module. Then on the right-side, is where you need to Copy and Paste the following VBA code.
Sub Set_Cities()
[city].ClearContents
val1 = [choice].Value
Range(val1).Name = "Cities_Current"
End Sub

#29c. Hold down <Alt> and hit Q. This will cause you to Exit from VBA Editor.

#30. Now go to the First DropDown box. Right-Click, and choose “Assign Macro”. At the bottom-centre of the “Assign Macro” window, opposite “Macros in:”, click the down-arrow, and choose “This Workbook”. Then double-click on “Set_Cities” (which is the VBA routine you just finished copying into Module1).

#31. Make a choice from the First DropDown box. This will activate the macro, and assign the name “Cities_Current” – which you’ll be referencing next (see #32) in the Second DropDown box.

#32. Now Right-Click on the Second DropDown box, and choose “Format Control”. Click on the “Control” tab. Opposite “Input range:”, enter: Cities_Current. Opposite “Cell link”, enter: city. The number of “Drop down lines:” is optional – i.e. it defaults to 8, but you should change this to 20 – to allow for the cities in the state of New York.

Well I hope that was relatively easy to follow. And of course I hope it helps.

Please advise if you have any difficulty (or if you don’t)

Regards, Dale Watson

 

[TonyJollans]

Dale,

Wow!

That is one of the most comprehensive sets of instructions I have ever seen on Tek-Tips! Most certainly worth a star!

jmj,

If you are interested, there are detailed instructions for doing this with Data Validation dropdowns on Debra Dalgleish's site. I suspect the techniques are more or less the same.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[DaleWatson123321]

Thanks, Tony

A STAR to you too for your link.

Regards, Dale Watson

 

[fumei]

Being a Word guy, rather than Excel, I find it odd (and it could VERY well be I am simply dumb) that form dropdowns only use ranges.

In any case, wonderful explanation Dale, and I certainly learned something!

However, I am wondering about not using ActiveX controls. Unless I do not understand (QUITE possible), the actual text of the states and cities have to be entered, as text, into ranges of cells. What if you did not really want that? I mean using up cell space with those lists of states and cities. If you used ActiveX controls you could populate directly, behind the scenes, with code. As in:

Two ActiveX comboboxes, populated with worksheet_activate. One with the list of states, the other stating the cities will show "here". The _Change event of the States combobox, clears the Cities combobox, and repopulates it using arrays. That way, the Cities combobox always automatocally reflects the selected choice of the States combobox. Using your example of Alaska, Florida and New York:

Private Sub Worksheet_Activate()
' load initial values into comboboxes

cboStates.AddItem " Please select a State  "
cboStates.AddItem "Alaska"
cboStates.AddItem "Florida"
cboStates.AddItem "New York"
cboStates.ListIndex = 0
cboCities.AddItem " Cities will appear here "
cboCities.ListIndex = 0
End Sub

Private Sub cboStates_Change()
Dim var
Dim i As Integer

' declare arrays
Dim AlaskaCities(3) As String
AlaskaCities(0) = "AlaskaCity1"
AlaskaCities(1) = "AlaskaCity2"
AlaskaCities(2) = "AlaskaCity3"
AlaskaCities(3) = "AlaskaCity4"

Dim FloridaCities(4) As String
FloridaCities(0) = "FloridaCity1"
FloridaCities(1) = "FloridaCity2"
FloridaCities(2) = "FloridaCity3"
FloridaCities(3) = "FloridaCity4"
FloridaCities(4) = "FloridaCity5"


Dim NewYorkCities(5) As String
NewYorkCities(0) = "NewYorkCity1"
NewYorkCities(1) = "NewYorkCity2"
NewYorkCities(2) = "NewYorkCity3"
NewYorkCities(3) = "NewYorkCity4"
NewYorkCities(4) = "NewYorkCity5"
NewYorkCities(5) = "NewYorkCity6"

' using selected State, populate Cities combobox
Select Case cboStates.ListIndex
    Case 0
        cboCities.Clear
        cboCities.AddItem " Cities will appear here  "
    Case 1
        cboCities.Clear
        For var = 1 To UBound(AlaskaCities)
            cboCities.AddItem AlaskaCities(i)
            i = i + 1
        Next
        cboCities.ListIndex = 0
    Case 2
        cboCities.Clear
        For var = 1 To UBound(FloridaCities)
            cboCities.AddItem FloridaCities(i)
            i = i + 1
        Next
        cboCities.ListIndex = 0
    Case 3
        cboCities.Clear
        For var = 1 To UBound(NewYorkCities)
            cboCities.AddItem NewYorkCities(i)
            i = i + 1
        Next
        cboCities.ListIndex = 0
End Select
End Sub

Gerry

 

[jmj]

Thanks for all the great info. I can actually see using both ways in different circumstances. (ie: with a huge city list I can easily cut/paste it using Dale's explanation).
Thanks again, this is all great stuff! The level of detail has been wonderful
J

 

[darkwolf1]

Dale I tried your version of it and keep getting errors when the macro runs....getting that close to making it work. Is there a mistake in the text you wrote here?? Either that or my eyes are seeing the mistake I am making when trying to duplicate the procedure.

Please let me know.
Thanks

 

[DaleWatson123321]

Hi darkwolf1,

I apologize for not noticing your posting until today. Unfortunately, I haven’t been able to spend as much time at Tek-Tips in recent months as I once did, as I’ve been given more “regular” work. I don’t often miss responses to my postings; perhaps it was due to being preoccupied with the regular work.

I want to start with a BIG “thanks” for pointing out that you encountered a problem. Yes, indeed, there was a “slight” but nonetheless, “significant” mistake in my instructions, as follows...

In my instructions, Item #23e read...
#23e. In O11, enter the formula: =M11, and then copy it down to M11:M15.

...whereas, it should have read...
#23e. In O11, enter the formula: =M11, and then copy it down to O11:O15.


I’ve checked out all the other instructions and I’m totally confident the above is the only instruction that would have caused a problem.

Please note, though, that Item #12 says “(There will be a formula later for D12)”, and it should have referred to “E12”. You’ll see that Item #28 (correctly) referred to copying the formula to “E12”.

ADDITIONAL SUGGESTION..
While the following might be “obvious” to those who have already “been down this road”, it hopefully will be useful for others who have not.

The above example was described such that ALL the information ends up on ONE sheet. In most applications, however, only the DropDown boxes are placed on the “end-user sheet”, while the rest of the data is moved to another sheet - a sheet that could be “hidden” if preferred.

Especially with the use of the range names, it becomes “quite easy” to move data around, including from one sheet to another.

To see “just how easy” this can be, you could follow these simple instructions..

#1 – Place your cursor on D7.

#2 – While holding down the <Shift> key, hit the <End> key, followed by the <Home> key. This will highlight all the data from D7 to the “end of the data” on the sheet.

#3 – (With the data highlighted), Hold down <Ctrl> and hit the “X” key. (This “cuts” the data)

#4 – Click on sheet-tab of a blank sheet – “Sheet2” for example.

#5 – Hold down <Ctrl> and hit the “V” key. (This “pastes” the cut data)

Now you can return to Sheet1 to try out the DropDown boxes, and confirm they still work.

I hope this helps.

Regards, Dale Watson

 

[darkwolf1]

Got it thanks we figured out the problems over the weekend, but good to have it all here