Testing for validation property
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)
"jrpfinch" wrote in message
...
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
On 2 May, 10:48, jrpfinch wrote:
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
|