Check if cell has data validation with VBA

neodjandre

Well-known Member
Joined
Nov 29, 2006
Messages
950
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Hi, This might help :-
Code:
MsgBox ActiveCell.SpecialCells(xlCellTypeAllValidation).Address
Mick
 
Upvote 0
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"
 
Upvote 0
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"

Hi Vog, is there a way not to delete the validation but just detect it... I have a feeling that the answer is no but I thought I would just ask... this is because I want to append data validation on a cell that already has data validation..
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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"
 
Upvote 0
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. ;)
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top