Hi, This might help :-
This is a discussion on Check if cell has data validation with VBA within the Excel Questions forums, part of the Question Forums category; Hello, How can I check if a cell has data validation with a VBA procedure? I want to do this ...
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...
Hi, This might help :-
The usual method is to delete any validation fitst, e.g.
Code:With Range("A1").Validation .Delete .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="4", Formula2:="6"
I don't know of an easy way. This is cumbersome
Code:Sub a() Dim X As Variant On Error Resume Next X = ActiveCell.Validation.Type On Error GoTo 0 If IsEmpty(X) Then MsgBox "Cell has no validation" Else MsgBox "Cell has validation" End If End Sub
This seems better
Code:Sub a() Dim r As Range Set r = Intersect(ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation), ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)) If Intersect(ActiveCell, r) Is Nothing Then MsgBox "Cell has no validation" Else MsgBox "Cell has validation" End If End Sub
This might do what you want
Code: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
I have tried ths, and it works fine when the sheet is not protected. However to protect th rest of the sheet contents I have protected my sheets, and now VBA does not find any cells with validation at all!
How can I check with VBA if all data is valid inside a protected sheet? I could hard-code the validation rules into VBA, but I already have the data Validation settings appropriate for each sheet.
Hello and welcome to MrExcel.
You can unprotect the sheet, run your code and reprotect it:
Code:ActiveSheet.Unprotect Password:="abc" ' 'code here ' ActiveSheet.Protect Password:="abc"
Please test any code on a copy of your workbook.
Thanks, that does work. I was hoping to avoid unprotecting the sheet, but if that's the only way then that's how we'll do it.