Using Validation.Type on a Range

B

blisspikle

I can do the following...
If Selection.Validation.Type = xlValidateList Then
Else
End If

Why can't I use...

If Worksheets(1).Cells(7,1).Validation.Type = xlValidateList Then
Else
End If

I am using Office 2003

Thanks,
 
N

Nigel

Actually they both work but only if the cell you reference or select has
validation applied. Any other cell will cause an error. When testing a
cell or range, you need to check if there is ANY validation first then
determine it type. See the following code as an example. Do not forget to
set the error trapping in your VBE to break on unhandled errors.

On Error Resume Next
With Range("B3")
x = .Validation.Type
If Err.Number = 0 Then
If x = 3 Then
MsgBox "Validation - is a list"
Else
MsgBox "Validation - not a list"
End If
Else
MsgBox "No validation"
End If
End With
On Error GoTo 0
 
Top