Check if cell has data validation with VBA

[neodjandre]

Hello,

How can I check if a cell has data validation with a VBA procedure?

I want to do this because I am using a procedure to add data validation and I get an error if the cell already contains data validation...

thanks
andy

 

[jamesmorris]

I have copied the code below from the post here above but it dos not have an "End Sub" or and "End With". I keep getting an error with the code.

Sub DELETE_VALIDATION()

With Range("B2:AV217").Validation
.DELETE
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="4", Formula2:="6"

 

[VoG]

Try

Sub DELETE_VALIDATION()

With Range("B2:AV217").Validation
    .Delete
    .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
    Operator:=xlBetween, Formula1:="4", Formula2:="6"

End With
End Sub

 

[Dash12]

I used this code and it works. Now if I wanted to also output the name of the list validated here, how do I do that?

Thank you

This might do what you want

Sub test()
    On Error Resume Next
        If ActiveCell.SpecialCells(xlCellTypeSameValidation).Cells.Count < 1 Then
            MsgBox "Active Cell does not have validation"
        Else
            MsgBox "Active cell has Validation"
        End If
    On Error GoTo 0
End Sub

 

[mikerickson]

I don't have Excel with me at the moment, but I think that if you look at the .Formula1 property of something inside ActiveCell.Validation it will have the range from which the validation list comes. The ObjectBrowser will help you get from a range object to the .Formula1 property.

 

[mikerickson]

I got home and did some testing.

I put List Validation on three cells (three styles of list source) the these are the returns from ThatCell.Validation.Formula1

=$A$1:$A$3

=MyNamedRange

Bob,Carol,Ted,Alice

 

[Dash12]

Would you know how I can translate this is in MATLAB? I am able to identify the type but not being able to then assign the values of the named range to a cell.

This might do what you want

Sub test()
    On Error Resume Next
        If ActiveCell.SpecialCells(xlCellTypeSameValidation).Cells.Count < 1 Then
            MsgBox "Active Cell does not have validation"
        Else
            MsgBox "Active cell has Validation"
        End If
    On Error GoTo 0
End Sub

 

[Roderick_E]

I know this an old thread but what about this solution...

Sub validationtest()
If InStr(ActiveCell.SpecialCells(xlCellTypeAllValidation).Address, ActiveCell.Address) = False Then
MsgBox ActiveCell.Address & " does not have validation"
Else
 MsgBox ActiveCell.Address & " cell has Validation"
        End If
End Sub