cells values with validation in another cell

srahuf · May 30th, 2008, 03:41 AM

How to set a validation in a cell and create a sublist based on the value of the current cell.

Example: cell A1 has a list of validations like "a","b","c" and when "a" is selected from the drop down menu it should set the validation in cell B1 like "apple","ball","cat". if "b" is chosed in cell A1, it should display validation list of "b", like "ant","bat","cat" and like the same for "c".

Thanks,
Rahuf

Shasur · May 30th, 2008, 04:05 AM

Rahuf

You can try using the Worksheet_Change event for that particuar sheet

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
       Select Case Target.Value
        Case "A"
            Range("B1").Validation.Delete
            Range("B1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:=xlBetween, Formula1:="cat,dog,ball"
        Case "B"
            Range("B1").Validation.Delete
            Range("B1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:=xlBetween, Formula1:="apple,baloon,car"
        Case Else
            Range("B1").Validation.Delete
            Range("B1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:=xlBetween, Formula1:="Else1,Else2,Else3"
       End Select
    End If
End Sub

Cheers
Shasur

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)