Test for "Data Validation" Excel 2000

[ChrisGreaves]

I dashed off this cheap'n'nasty this afternoon at a client site.
I know that there must be a better way of detecting if a cell has data validation set.
I just don't know what it is!

Code: Select all

Function blnValidation(rngInput As Range) As Boolean
    On Error GoTo Failed
    If Len(rngInput.Validation.Formula1) = 0 Then
    Else
    End If
    blnValidation = True
Failed:
'Sub TESTblnValidation()
'    MsgBox blnValidation(ActiveCell)
'End Sub
End Function

[HansV]

Your code can be shortened to

Code: Select all

Function blnValidation(rngInput As Range) As Boolean
    On Error Resume Next
    blnValidation = (Len(rngInput.Validation.Formula1) > 0)
End Function

Another version:

Code: Select all

Function blnValidation(rngInput As Range) As Boolean
  On Error Resume Next
  blnValidation = (rngInput.Validation.Type >= 0)
End Function

Note: if rngInput is a multi-cell range, the function will only return True if all cells within the range have the same type of validation.

[ChrisGreaves]

Note: if rngInput is a multi-cell range, ...

Thanks for this and the streamlining, Hans.
The multi-range I'll deal with once I move the code into my library.
(My original code read On Error ...: Debug.Print rngInput.Validation.Formula1 !)
My biggest surprise was that there seemed no built-in property for detecting if Data validation was set on a cell; A quick Google Search turned up lots of references to "Go To; Special; data validation" but no VBA testing.