Testing for Cell Validation

  • Thread starter Thread starter djrforb
  • Start date Start date
D

djrforb

For a newby to VBA programing; anyone know a neat way of testing in VB
if a cell has data validation turned on.

Have only been able to test with an error trap on Validation.Type


Dunca
 
Dim cell as Range
set cell = Range("B9")
If not intersect(Cell, Cells.SpecialCells(xlCellTypeAllValidation)) is
nothing then
msgbox Cell.Address & " contains data validation"
Else
msgbox cCell.Addres & " does not contain data validation"
End if
 
Tom:
I got this working IF TRUE, but IF FALSE I get
RT error 1004
No cells were found, <<On the Intersect() line.
Any Suggestions?
Jim

Tom Ogilvy said:
Dim cell as Range
set cell = Range("B9")
If not intersect(Cell, Cells.SpecialCells(xlCellTypeAllValidation)) is
nothing then
msgbox Cell.Address & " contains data validation"
Else
msgbox cCell.Addres & " does not contain data validation"
End if
Click to expand...
 
Hi Jim,

Tom was demonstrating a technique rather than supplying final code.
Tom has pointed out many times to others that the SpecialCells method
throws an error if no cells are found. Had this been production code, Tom
would have trapped the resultant error.

Try:

Dim cell As Range, Rng As Range
Set cell = Range("B9")

On Error Resume Next
Set Rng = Intersect(cell, Cells. _
SpecialCells(xlCellTypeAllValidation))
On Error GoTo 0

If Not Rng Is Nothing Then
MsgBox cell.Address & " contains data validation"
Else
MsgBox cell.Address & " does not contain data validation"
End If


---
Regards,
Norman



Jim May said:
Tom:
I got this working IF TRUE, but IF FALSE I get
RT error 1004
No cells were found, <<On the Intersect() line.
Any Suggestions?
Jim
Click to expand...
 
I think this might be another option as a function:

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

Debug.Print HasValidationQ([A1])
 
Norman,
Thanks for taking the time to get into this;
I used your code and am "error-free"...
Jim May
 
Back
Top