Results 1 to 5 of 5

Cascading down down lists

Threaded View

  1. #1
    Registered User
    Join Date
    03-22-2010
    Location
    Ballarat, Australia
    MS-Off Ver
    Excel 2003
    Posts
    31

    Cascading down down lists

    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
    Attached Files Attached Files
    Last edited by troygeri; 06-07-2011 at 10:48 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1