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


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

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