Check To See If A Cell Have Data Validation

[markkramer]

Hi!

Is there an expression that can be used to check to see if a cell has data validation associated with it? In psuedo-code, I'm looking to do this:

If Range("A1").Validation = False
msgbox("Hey! You got rid of the data validation!")
Endif

 

[Tom Urtis]

Sub Test1()
On Error Resume Next
If ActiveCell.Validation.Type <> 0 Then
If Err.Number <> 0 Then
Err.Clear
MsgBox _
"Cell " & ActiveCell.Address(0, 0) & _
" does not have data validation.", _
48, "No DV here"
Else
MsgBox _
"Cell " & ActiveCell.Address(0, 0) & _
" has data validation.", _
64, "Seek and you shall find."
End If
End If
End Sub

 

[markkramer]

That's it, Tom!

Thanks!

M

 

[Joe Was]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myCol%
Dim myRow&

If Selection.Count > 1 Then Exit Sub

myCol = Target.Column
myRow = Target.Row

On Error GoTo myErr
If Not IsMissing(Cells(myRow, myCol).Validation.Operator) Then _
MsgBox "This cell has Data Validation!", vbInformation + vbOKOnly, "Data Validation!"
Exit Sub

myErr:

MsgBox "Hey! You got rid of the data validation!", vbExclamation + vbOKOnly, "Validation Error!"
End Sub

 

[markkramer]

Thanks Joe! That works too!