Clear data validation list with new selection (VBA)

[gmelchor]

Hey everyone,


I'm currently learning to do VBA (still in the very beginner stage) and I am trying to to write some code regarding three dependent data validation lists. Let's call them "Country", "State", and "City".


I want the "City" list to reset when I change the "State" list. Similarly, I want the "City" and "State" lists to reset when I change the "Country" list.


I found some VBA code that enables me to reset one of the lists when I change another, but I haven't been able to extend the code to do exactly what I want.


I appreciate your help and I hope to learn from it!


P.S. Here is the code I found. Say "E32" is "State" and "G32" is "City".


Option Explicit

Public Prior As String


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

Application.EnableEvents = False

If Not Intersect(Target, Range("E32")) Is Nothing Then

If Target <> "" And Target <> Prior Then Range("G32").ClearContents

End If

Application.EnableEvents = True

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("E32")) Is Nothing Then _

Prior = Target.Text

End Sub

 

[SirJB7]

Hi, gmelchor!

Does this help?

http://chandoo.org/forums/topic/data-validation-list-automatically-shows-the-first-item-in-the-list

Regards!

 

[gmelchor]

SirJB7, thank you for the quick response. That is exactly what I want to do, however I want to extend it to a third dependent validation. The logic is right, I just don't know how to extend it.


Here is a sample workbook that shows what I'm talking about.


Thank you for your help!

 

[jeffreyweir]

I have recently whipped up code that does this. See sample file.


https://www.dropbox.com/s/8tfgn3t2l0m5lm5/Dynamic%20Dependent%20dropdowns%2020130716.xlsm

 

[gmelchor]

Jeff,


Very impressive! I'll work to adapt your code to my workbook. I like how it resets the list to "Choose..." when it clears instead of a blank cell.


Thank you!

 

[jeffreyweir]

Glad you like it. The code was surprisingly hard to get right. Note that it relies on you having your validation lists and your dropdown lists organized in Excel tables.