Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Check if cell has data validation with VBA

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 ...

  1. #1
    Board Regular
    Join Date
    Nov 2006
    Posts
    808

    Default Check if cell has data validation with VBA

    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

  2. #2
    Board Regular
    Join Date
    Jan 2008
    Posts
    7,963

    Default Re: Check if cell has data validation with VBA

    Hi, This might help :-
    Code:
    MsgBox ActiveCell.SpecialCells(xlCellTypeAllValidation).Address
    Mick

  3. #3
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,213

    Default Re: Check if cell has data validation with VBA

    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"

  4. #4
    Board Regular
    Join Date
    Nov 2006
    Posts
    808

    Default Re: Check if cell has data validation with VBA

    Quote Originally Posted by VoG View Post
    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..

  5. #5
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,213

    Default Re: Check if cell has data validation with VBA

    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

  6. #6
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,213

    Default Re: Check if cell has data validation with VBA

    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

  7. #7
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,479

    Default Re: Check if cell has data validation with VBA

    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

  8. #8
    New Member
    Join Date
    Sep 2009
    Posts
    4

    Default Re: Check if cell has data validation with VBA

    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.

  9. #9
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,213

    Default Re: Check if cell has data validation with VBA

    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"
    HTH, Peter
    Please test any code on a copy of your workbook.

  10. #10
    New Member
    Join Date
    Sep 2009
    Posts
    4

    Default Re: Check if cell has data validation with VBA

    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.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com