How to detect a Validation Object in a Cell using VBA?

[keithb]

How can you test a cell in VBA to determine if if contains a Validation
Object? How can you delete an existing Validation Object in VBA?

Thanks,

Keith

 

[Dave Peterson]

Saved from a previous post:

Depending on what you're doing, it may be easier to just delete it first and add
it back the way you want.

dim myCell as range
set mycell = range("a1")
mycell.validation.delete
'add it back here...

And another way to test to see if the cell has validation:

Option Explicit
Function HasValidation(rng As Range) As Boolean

Dim myValidationType As Long

Set rng = rng(1)

myValidationType = -1
On Error Resume Next
myValidationType = rng.Validation.Type
On Error GoTo 0

HasValidation = CBool(myValidationType > -1)

End Function

Sub testme()

With ActiveSheet
MsgBox HasValidation(.Range("a1")) & vbLf & HasValidation(.Range("b1"))
End With

End Sub