Saturday, May 7, 2011

Data Validation List based on another data validation list

Have you ever thought how simple life could be if you could make your spreadsheets act more like a form without going through the trouble of creating one?  In today's example you can create a list of values in one field where another list is populated based on what the user selects!
Sub list2()
    With Cells(1, 2).Validation
        If Cells(1, 1) = "one" Then
            .Add Type:=xlValidateList, Formula1:="A, B, C"
            ElseIf Cells(1, 1) = "two" Then
                .Add Type:=xlValidateList, Formula1:="D, E, F"
            ElseIf Cells(1, 1) = "three" Then
                .Add Type:=xlValidateList, Formula1:="G, H, I"
        End If
    End With
End Sub
The above example will only refresh when the macro is run.  If you want this to run as soon as a value is chosen you may want to set this into the worksheet where an event will trigger it to run.