Check if cell has data validation with VBA

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Check if cell has data validation with VBA

  1. #1
    Board Regular
    Join Date
    Nov 2006
    Posts
    860
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    12,883
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Check if cell has data validation with VBA

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

  3. #3
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    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
    860
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    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
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    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
    21,372
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

User Tag List

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