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.
If you wanted to, you could add some conditional formatting to highlight the blank cells that need to be filled in.
Get the Code
Enter your email address below to download the sample workbook.
Download the workbook with the code for you to check out.
Excel Dependent Data Validation
Ravi Shankar
I am getting Runtime error if this code is used with data validation for data entry in first cell through dependent list. Let me explain. If I type anything in the first cell apart from what is there in the drop down list, the data validation setting of error message set by me is triggered. Then if I try to undo then VBA run time error 1004 msg comes up which directs to Application.Undo at Line 38 of your original code. I can make out the error in VBA is because the error msg of data validation is not captured by it.
Can you please tell how to handle the error in VBA.
Thank.
Philip Treacy
Hi Ravi,
Why are you typing into a DV cell? If you do type in something that isn’t in the list, the error alert message should pop up and when you acknowledge that, the typed value in the cell should be cleared.
I can’t reproduce your error. Please start a topic on the forum and attach your file so we can debug it.
Regards
Phil
Tuan Kriel
Thanks for this neat procedure.
Would you please help me with the code to add a condition before the clear contents instruction?
I would like to test if the current downstream value is available in the array for the parent value.
If it is, then do not clear contents else do clear contents.
ex. Parent=168, array for 168 =(37.61; 43.37; 49.13; 54.86)
Parent=219, array for 219 =(43.37; 49.13; 54.86)
So if 168 gets replace by 219 only clear the child if it is 37.61 because the array for 219 does not contain the value 37.61
Catalin Bombea
Hi Tuan,
You can simply loop through the initial array and check if the items are in the new array.
Application.Match should be enough:
application.match(37.61, array(43.37, 37.61, 49.13, 54.86),0) will return the position of the item if there is a match, or “Error 2042” if there is no match.
InitialArray=ParentArray
NewArray=NewParentArray
Dim Counter as Long
For i=0 to Ubound(initialArray)
If IsNumeric(Application.match(InitialArray(i),NewParentArray,0)) then
Redim Preserve FinalArray(0 to Counter)
FinalArray(Counter)=InitialArray(i)
Counter=Counter+1
Next
At the end of the loop, you will have a FinalArray where all the items from InitialArray can be found in NewParentArray. Note that not only the InitialArray can contain items that cannot be found in NewParentArray, also the NewParentArray an contain items not in the InitialArray, this case is not handled in code as there is no specification of what should happen for this case.