I have created a spreadsheet with a set of three cascading drop down lists vased on the approach outined in http://www.contextures.com/xlDataVal02.html
I have the following
1. Concept Area (List 1)
2. Subject area within each concept area (List 2)
3. Entity within each subject area (List 3)
Issue I have is I have VB on the worksheet to handle when a drop down is reset, it clears the next dependant drop down (here is the code)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oneCell As Range
On Error GoTo ErrorOut
For Each oneCell In ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation)
If Not oneCell.Validation.Value Then oneCell.ClearContents
Next oneCell
ErrorOut:
Application.EnableEvents = True
On Error GoTo 0
End Sub
However if I reset the value in list 1 I need it to clear list 2 and 3, the above code only clears list 2, not 2 and 3. My workaround was to add an if statement to list 3 data validation source (e.g. =if(B2="","",remainingcode). The problem is this approach seems to cause the whole spreadsheet to freeze and go into a loop when I reset values.
Here is a link to the spreadsheet, would appreciate any assistance in resolving this. The tab that has the drop downs is called "CascadeSelect", the data supporting the drop down (i.e. named ranges) are in Sheet2
Bookmarks