Data validation: Value "b" in another cell allows any value

[tohtorisalainen]

If value of cell A1 is "a", then I want data validation (list of choices) for cell B1.
If value of cell A1 is "b", then any value can be entered into cell B1.

Data validation doesn't automatically support this.

How to do this?

 

[vaskov17]

here is a macro that might help:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" And Range("A1").Value = "a" Then
        With Range("B1").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$C$1:$C$5"
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
            .ShowError = True
        End With
    ElseIf Target.Address = "$A$1" And Range("A1").Value <> "a" Then
        Range("B1").Validation.Delete
    End If
End Sub

basically whenever you change the value of A1 to "a" it will enable validation for B1 and will use the cells "C1:C5" as the list of allowed values, if you change A1 to anything other that "a" the validation is disabled

 

[tohtorisalainen]

Thanks, this should do the trick!

I assume that there isn't any nice way of doing this without macros? Or is there?

 

[vaskov17]

I can't think of one, but maybe someone else might be able to.