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.
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
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
