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

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

--
Biff
Microsoft Excel MVP


"Roady" wrote in message
...
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

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

On Mon, 28 Jan 2008 12:35:01 -0800, Roady
wrote:

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

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" wrote:

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

--
Biff
Microsoft Excel MVP


"Roady" wrote in message
...
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

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.

--
Biff
Microsoft Excel MVP


"Roady" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP


"Roady" wrote in message
...
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

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)



--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Roady" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP


"Roady" wrote in message
...
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