Clear Downstream Dependent Data Validation Lists

Clearing Downstream Dependent Data Validation Lists

Following on from Mynda's post last week on Dependent Data Validation, I've written a little VBA that enhances the functionality.

What this code does is to clear any cells dependent on the value of another data validation list. I'll call these downstream cells.

So if I change the Country, I'm going to clear the values in the State/County and City cells. This doesn't normally happen in Excel, it just leaves the State/County and City as they were, and leaves it up to you to change them.

The first thing we need to do is convert the data into a table, with the name LocationsTable.

This allows us (amongst other things), to use structured references to refer to the table, or parts of it.

Detecting a Change

To detect a change in one of the cells using data validation, I'm going to use the Worksheet_Change event, and so the code will be in the Sheet1(Multi DV) module in the workbook you can download below.

Worksheet_Change is triggered when any change is made on the sheet, so my code must check that the cell being changed is one that I want to run the code for, and ignore other changes.

I need to check that the cell being changed is in either the Country column or the State/County column of the table. I do this using structured references to those columns, and checking to see if the Target (the changed cell) intersects those columns.


    If Intersect(Target, [LocationsTable[Country]]) Is Nothing And Intersect(Target, [LocationsTable[State/County]]) Is Nothing Then
    
        Exit Sub
      
    End If

If you click on a cell that uses data validation, and then click on the same value that is already in the cell, Excel sees this as a change.

So if the country is Australia, and you then click on the cell and select Australia, you haven't actually changed the country but Excel still executes the Worksheet_Change event.

We need to take this into account so we don't clear the State/County and City when the Country hasn't changed.

When a change is made we store the new value in a variable NewValue. Then we call Application.Undo to undo that change. Our cell now contains whatever value was there before we changed it.

Now we compare the NewValue and the old value, if they are different then a change has been made and the code can continue running, otherwise we can exit the code.


    NewValue = Target.Value
    Application.Undo
    
    ' If the old value is different to the new value
    '
    If Target.Value <> NewValue Then
    
        ' Assign the Newvalue to the changed cell
        '
        ' More code goes here
        '

Clearing the Downstream Cells

Once we know a change has happened, the code uses OFFSET to clear the cells downstream.


If Not Intersect(Target, [LocationsTable[Country]]) Is Nothing Then
        
   ' Clear the two downstream cells which are
   ' in the State/Country and City columns
   '
   Target.Offset(, 1).ClearContents
   Target.Offset(, 2).ClearContents
           
   Else
        
   ' Otherwise just clear one downstream cell
   ' which is in the City column
   '
   Target.Offset(, 1).ClearContents
            
End If

When you change the Country, the State/County and City are cleared, and the next cell that needs to be completed is selected.

If you just change the State/County, the City is cleared.