Testing for validation property
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
|