Testing for Cell Validation
 

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

--
djrfor
-----------------------------------------------------------------------
djrforb's Profile: http://www.excelforum.com/member.php...fo&userid=1486
View this thread: http://www.excelforum.com/showthread.php?threadid=26492

Testing for Cell Validation
 

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

--
Regards,
Tom Ogilvy


"djrforb" wrote in message
...

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

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


Duncan


--
djrforb
------------------------------------------------------------------------
djrforb's Profile:
http://www.excelforum.com/member.php...o&userid=14863
View this thread: http://www.excelforum.com/showthread...hreadid=264928

Testing for Cell Validation
 

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

--
Regards,
Tom Ogilvy


"djrforb" wrote in message
...

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

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


Duncan


--
djrforb
------------------------------------------------------------------------
djrforb's Profile:
http://www.excelforum.com/member.php...o&userid=14863
View this thread:
http://www.excelforum.com/showthread...hreadid=264928

Testing for Cell Validation
 

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" wrote in message
news:t%O6d.4037$Hz.814@fed1read04...
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" wrote in message
...
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

--
Regards,
Tom Ogilvy


"djrforb" wrote in message
...

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

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


Duncan


--
djrforb
------------------------------------------------------------------------
djrforb's Profile:
http://www.excelforum.com/member.php...o&userid=14863
View this thread:
http://www.excelforum.com/showthread...hreadid=264928

Testing for Cell Validation
 

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])

--
Dana DeLouis
Win XP & Office 2003


"djrforb" wrote in message
...

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

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


Duncan


--
djrforb
------------------------------------------------------------------------
djrforb's Profile:
http://www.excelforum.com/member.php...o&userid=14863
View this thread: http://www.excelforum.com/showthread...hreadid=264928

Testing for Cell Validation
 

Norman,
Thanks for taking the time to get into this;
I used your code and am "error-free"...
Jim May


"Norman Jones" wrote in message
...
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" wrote in message
news:t%O6d.4037$Hz.814@fed1read04...
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" wrote in message
...
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

--
Regards,
Tom Ogilvy


"djrforb" wrote in message
...

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

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


Duncan


--
djrforb

------------------------------------------------------------------------
djrforb's Profile:
http://www.excelforum.com/member.php...o&userid=14863
View this thread:
http://www.excelforum.com/showthread...hreadid=264928