Validation.Type

[Roderick_E]

Apparently some cells don't have any validation types set??? Thus my following code throws an error popup?

Sub valtype()
MsgBox ActiveCell.Validation.Type
End Sub

ERROR:
Runtime error 1004
Application-defined or object-defined error

I want to check for this potentiality and then do something else.

Please help. Thanks

 

[sericom]

You could do something like this

vType = ""
On Error Resume Next
vType = Range("A1").Validation.Type
If vType <> "" Then
    MsgBox vType
Else
    MsgBox "No validation set"
End If

 

[JoeMo]

This UDF returns True if all cells in its argument have some form of data validation.

Function HasDataValidation(R As Range) As Boolean
'returns true if all cells in R have Data Validation, even if the type of DV varies among the cells in R
Dim c As Range, T As Boolean
For Each c In R
    On Error Resume Next
    T = c.Validation.Type
    If Err.Number <> 0 Then
        Err.Clear
        HasDataValidation = False
        Exit Function
    End If
Next c
HasDataValidation = True
End Function

 

[Roderick_E]

Thanks all. I went with JoeMo's UDF since I can deploy it more globally even though sericom answered the exact issue.