Suppress drop-down list dependent on value chosen in another drop-down list

**ptmald** · 10-10-2013, 12:56 PM

I am simplifying this post (and the attached file) from my earlier post in hopes of getting some help. I have been pounding my head against the wall for days on this, still can't get it to work.

I modified some code from another person's post to try to achieve what I was looking to do:
http://www.excelforum.com/excel-form...selection.html

I have a table, there are multiple columns.
I have two columns [Category] & [Detail], each of them has a drop down list.
The values displayed in the drop down for [Detail] are dependent on what was chosen from the drop down in [Category].
I have used the INDIRECT function to properly display the right list of values in the [Detail] column.

I have a value in [Category], "Mineral" for which I do not want the drop down to appear in [Detail], all other values should activate the drop down list in [Detail] and invoke the INDIRECT function.

I was able to use the code from the other post to get the dropdown suppressed in the first row of my table, and to get the INDIRECT call working for the valid values. (Please note this worksheet will eventually be protected, in order to get the INDIRECT call portion of the macro to work I had to "unlock" the cell first - via the macro)
Can't figure out how to get it to work on the rest of the rows in the table (which can vary depending on if the user is adding or deleting rows).

Including the code as I have modified it to work. E6 is the location of first row [Category], F6 is the location of first row [Detail]
Also note, including the code which clears the info in [Detail] should the value of [Category] change.

Private Sub Worksheet_Change(ByVal Target As Range)

'original code to clear dependant cell if original cell value changes
Dim rngAllParentCells As Range
Dim rngDepCells As Range
Dim rngCell As Range

If Target.Cells.Count > 1 Then Exit Sub
Set rngAllParentCells = Range("Table1[Category]")
Set rngDepCells = Intersect(Target, rngAllParentCells)

If Not rngDepCells Is Nothing Then
    For Each rngCell In rngDepCells.Cells
        'Move 1 cell to the right and clear contents
        rngCell.Offset(RowOffset:=0, ColumnOffset:=1).ClearContents
    Next rngCell
End If


Set rngAllParentCells = Nothing
Set rngDepCells = Nothing
Set rngCell = Nothing

'New code to suppress dropdown
    If Target.Address = "$E$6" Then
    
        Select Case Target.Value

        Case Is = "Animal", "Vegetable"
        
    Dim PW As String
    PW = "orderform"
    With Sheets("Order")
        .Unprotect _
                Password:=PW
    
        
            With Range("F6").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:="=INDIRECT(E6)"

                .InCellDropdown = True
            End With
            Range("F6").Select
        

            .Protect _
                Password:=PW
    End With


        Case Is = "Mineral"

            Range("F6").Value = "N/A"
            With Range("F6").Validation
                .Delete
            End With
            
        
        End Select
    End If
End Sub

I am attaching a sample file.
The password to unprotect, is "orderform"

thanks!

**ptmald** · 10-17-2013, 07:17 PM

I figured this one out. The code above has changed to:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngAllParentCells As Range
Dim rngDepCells As Range
Dim rngCell As Range


If Target.Cells.Count > 1 Then Exit Sub
Set rngAllParentCells = Range("Table1[Category]")
Set rngDepCells = Intersect(Target, rngAllParentCells)

If Not rngDepCells Is Nothing Then
         
    For Each rngCell In rngDepCells.Cells
         rngCell.Offset(RowOffset:=0, ColumnOffset:=1).ClearContents
         
            Dim PW As String
            PW = "orderform"
        With Sheets("Order")
            .Unprotect _
            Password:=PW
            
        Select Case Target.Value
         
            Case Is = "Mineral"
                    rngCell.Offset(RowOffset:=0, ColumnOffset:=1).Value = "N/A"
                With rngCell.Offset(RowOffset:=0, ColumnOffset:=1).Validation
                    .Delete
                End With
                rngCell.Offset(RowOffset:=0, ColumnOffset:=0).Select
                
            Case Is = "Animal"
                With rngCell.Offset(RowOffset:=0, ColumnOffset:=1).Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:="=Animal"
                    .InCellDropdown = True
                End With
                rngCell.Offset(RowOffset:=0, ColumnOffset:=1).Select
                
            Case Is = "Vegetable"
                With rngCell.Offset(RowOffset:=0, ColumnOffset:=1).Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:="=Vegetable"
                    .InCellDropdown = True
                End With
                rngCell.Offset(RowOffset:=0, ColumnOffset:=1).Select
                
        End Select
        
            .Protect _
            Password:=PW
        End With
    
    Next rngCell
End If



Set rngAllParentCells = Nothing
Set rngDepCells = Nothing
Set rngCell = Nothing

End Sub

No longer able to call the INDIRECT function, each option must be explicitly specified in the macro. This works fine for a smaller dependent list, but could be problematic for a longer list, I'm sure there is a more elegant way to solve this, but this works for me. Hopefully someone else looking for similar functionality will benefit from my labors on this one.
functioning solution attached.