Testing for validation property

  • Thread starter Thread starter jrpfinch
  • Start date Start date
J

jrpfinch

I would like to test if a cell is validated using a list. However,
the Validation property does not exist by default - it seems that only
validated cells have this property. The code below, therefore,
results in a "Application-defined or object-defined error" when you
change the value of a non-validated cell. I would be grateful to
know what the best way to run this test is. I guess you could use 'On
Error', although this seems hacky.

Many thanks

Jon


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Validation.Type = xlValidateList Then
MsgBox "Do something"
End If




End Sub
 
For the record, this is the only method found using a google search.
There must be a better way!

Dim mycel As Range
Dim vald As Variant
Set mycel = Range("D2")
On Error Resume Next
vald = mycel.Validation.Type
On Error GoTo 0
If vald <> Empty Then
MsgBox "yes the cell have validation"
Else
MsgBox "no no, the cell is general."
End If
 
Seems pretty simple (easy) to me, how easy do you want it? Put it in a
function, and you have a one-liner.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

For the record, this is the only method found using a google search.
There must be a better way!

Dim mycel As Range
Dim vald As Variant
Set mycel = Range("D2")
On Error Resume Next
vald = mycel.Validation.Type
On Error GoTo 0
If vald <> Empty Then
MsgBox "yes the cell have validation"
Else
MsgBox "no no, the cell is general."
End If
 
Back
Top