Perform a via VBA added validation in Excel

1
votes

I have a problem concerning Excel 2010 VBA. I do not know the correct Englisch Excel terms because I use a German Excel Version. But I will try my best. Sorry for that.

In VBA I set a data validation for one cell (a list selection). If the cell has already set a value, it remains in the cell nevertheless it might not fulfill the data validation. Is it possible to perform the just set data validation by VBA and if the validtaion is not fullfilled clear the value in the cell?

Thanks in advance.

2
validationvbaexcelexcel-2010
show some code because its unclear what youre asking - user2140173

2 Answers

1
votes

Even showing some code would help cross the language barrier and help us answer this question; however, I assume you are asking if you can evaluate the validation itself with VBA code. You can.

The Validation object has a .Value property that can be evaluated:

If Not validation.Value Then
    cell.ClearContents
End If
0
votes

Give this a try:

Sub PlaceDV()
    Dim r As Range
    Dim v As String
    sValid = "alpha,beta,gamma"
    Set r = ActiveCell
    v = CStr(r.Value)
    If InStr(1, sValid, v) = 0 Then
        r.ClearContents
    End If
    With r.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=sValid
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub