dependant lists- trigger a blank when 1st one is changed?

[Roady]

Hello:
I currently have two columns:
Column A is Countries
Column B is Cities associated with those countries in Column A

I have the data validation set up so that when you select Australia, for
example, in Column A, it limits/narrows the drop down list in Column B to
only show choices of cities in Australia (Sydney, Brisbane, etc.).

Here's my problem: when I have already selected the Country and city
(Australia and Sydney for example) and then later decide to change the
Country choice, it still shows the old city that I chose for Australia. So, I
may change the country to Japan, but it will still show Sydney in Column B
unless I click on the Column B drop down.

In order to avoid careless mistakes, I would like to have it automatically
trigger the Column B to go blank if Column A is changed. Thus prompting the
person to know that they have to fill a choice in for city from the Column B
drop down.
Is this possible?

Much thanks!!
JR

 

[T. Valko]

You can do this with an event macro.

Assume the primary drop down list is in cell A2 and the dependent list is in
B2.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo sub_exit
If Target.Address = "$A$2" Then
Range("B2").ClearContents
End If
sub_exit:
Application.EnableEvents = True
End Sub

To use this:
Select the sheet where you want this to happen
Right click the sheet tab and select View code
Copy/paste the code above into the window that opens
Close the window to return to Excel

 

[Gord Dibben]

JR

Copy/paste this to the sheet module by right-click on sheet tab and "View Code"
to open the module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1::A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value <> "" Then
.Offset(0, 1) = ""
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

When user re-selects from Country dropdown, Cities dropdown will go blank in
same row.


Gord

 

[Roady]

Hi T-

So it worked like magic for the first row but then wasn't working for all
subsequent rows. I copied and pasted exactly what you had written except
switching out A & B for O and U, respectively (since those are the actual
columns).

any thoughts on troubleshooting?
Thanks,
Jen

 

[T. Valko]

See Gord's example. He wrote it to work for a range of cells where I wrote
mine to work on only a single cell.

 

[T. Valko]

Here's Gord's code modified to work on column O and column U:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("O1:O100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value <> "" Then
.Offset(0, 6) = ""
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Adjust the range as needed in this line:

If Intersect(Target, Me.Range("O1:O100"))........

Column U is defined in this line:

..Offset(0, 6)