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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <Roady@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0EF5A94F-295A-46FB-8B04-15C813941319@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Quality of life in the UK
    ... The quality of life we have found here is vastly superior to what we had in Sydney; we couldn't afford to buy a house in Sydney because the housing prices were in excess of what we could afford without him having to commute 3 hours between home and the city. ... There is the trade-off though in it being isolated from the rest of Australia, being closer to south-east Asia than it is to Sydney. ... Melbourne is more like Sydney than Perth, but again, it has the feel of a big country town rather than a state capital. ... only one in school, and we struggled to find a school to meet her exceptional needs, yet the first school we tried here in England was superior to anything else we found in Sydney within the state and private system; she's attending a state school here which is teaching at a level at least a year ahead of what she was being taught in her "gifted and talented" class in Sydney. ...
    (uk.people.parents)
  • Re: Long term flood info
    ... you might die of boredom in Perth. ... Moving to a large metropolitan city MAY not be what we are after. ... > Perth Tourist Information - Western Australia, ... I am in the minority in my own country. ...
    (misc.survivalism)
  • Re: dependant lists- trigger a blank when 1st one is changed?
    ... So it worked like magic for the first row but then wasn't working for all ... only show choices of cities in Australia. ... Country choice, it still shows the old city that I chose for Australia. ...
    (microsoft.public.excel.misc)
  • Re: Free Weather Exchange Network software for your local conditions
    ... Weather for any City, State, or ZIP Code or Airport Code or Country". ... Then I clicked the "Go" box and got several sites in and around Sydney. ... "Perth, Australia" ...
    (rec.windsurfing)
  • Re: The globally fucked climate.
    ... Not unlike Sydney then. ... I've always wanted to go to Australia" I recommend that if they simply ... Probably the most over-rated city on earth. ... shithole city is somehow better than other shithole cities. ...
    (rec.sport.rugby.union)