Determine if a cell has datavalidation
 

Hi,

Is there anyway to determine if the cell has data validation defined ?.
Something similar to range.hasformula. I want to turn incelldropdown to
false if datavalidation is defined for the cell. I need this done to
prevent selection from the dropdown list of protected , locked cells
when the file has been opened in readonly.

Thank you.


---
Message posted from http://www.ExcelForum.com/

Determine if a cell has datavalidation
 

Sub testit()
Dim blnHasValidation As Boolean

On Error GoTo e
blnHasValidation = False
With ActiveCell.Validation: blnHasValidation = CBool(.Type = 0 Or .Type
< 0): End With
e: On Error GoTo 0

MsgBox blnHasValidation
End Sub


"mohsinb" wrote in message
...
Hi,

Is there anyway to determine if the cell has data validation defined ?.
Something similar to range.hasformula. I want to turn incelldropdown to
false if datavalidation is defined for the cell. I need this done to
prevent selection from the dropdown list of protected , locked cells
when the file has been opened in readonly.

Thank you.


---
Message posted from http://www.ExcelForum.com/

Determine if a cell has datavalidation
 

After putting brain in gear.. here's another solution:

Sub testit()
Dim blnHasValidation As Boolean

With ActiveSheet
blnHasValidation = Not
Intersect(.Cells.SpecialCells(xlCellTypeAllValidat ion), ActiveCell) Is
Nothing
End With

MsgBox blnHasValidation
End Sub


"Rob van Gelder" wrote in message
...
Sub testit()
Dim blnHasValidation As Boolean

On Error GoTo e
blnHasValidation = False
With ActiveCell.Validation: blnHasValidation = CBool(.Type = 0 Or
..Type
< 0): End With
e: On Error GoTo 0

MsgBox blnHasValidation
End Sub


"mohsinb" wrote in message
...
Hi,

Is there anyway to determine if the cell has data validation defined ?.
Something similar to range.hasformula. I want to turn incelldropdown to
false if datavalidation is defined for the cell. I need this done to
prevent selection from the dropdown list of protected , locked cells
when the file has been opened in readonly.

Thank you.


---
Message posted from http://www.ExcelForum.com/

Determine if a cell has datavalidation
 

Thanks Rob.

I settled for a variation of the ''On Error" method to determine i
there is data validation for a cell. Works good. All set

--
Message posted from http://www.ExcelForum.com

Determine if a cell has datavalidation
 

I think this might be another way:

Function HasValidation(rng As Range) As Boolean
On Error Resume Next
HasValidation = rng.Validation.AlertStyle
End Function

Debug.Print HasValidation(ActiveCell)
Debug.Print HasValidation(Range("B1"))

--
Dana DeLouis
= = = = = = = = = = = = = = = = =


"mohsinb" wrote in message
...
Hi,

Is there anyway to determine if the cell has data validation defined ?.
Something similar to range.hasformula. I want to turn incelldropdown to
false if datavalidation is defined for the cell. I need this done to
prevent selection from the dropdown list of protected , locked cells
when the file has been opened in readonly.

Thank you.