Using Validation.Type on a Range
 

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,

Using Validation.Type on a Range
 

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

--

Regards,
Nigel




"blisspikle" wrote in message
...
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,

Using Validation.Type on a Range
 

Thank you,

Seems kind of silly, when you go into a cell not using validation and
open Validation Form it has "Any Value" in the Allow Combobox, so I
just assumed that validation was on in every cell, but allowing "any
value". Anyways, thank you for the answer.




On Jan 8, 12:44*am, "Nigel" wrote:
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

--

Regards,
Nigel


"blisspikle" wrote in message

...



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,- Hide quoted text -

- Show quoted text -