Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Data validation - applying & listing via VBA

  1. #1
    Board Regular Dr. Demento's Avatar
    Join Date
    Nov 2010
    Location
    Skipping stones off Charon's Ferry
    Posts
    504
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Data validation - applying & listing via VBA

    I have a table and I'd like to apply data validation to the entire table column based on the header (ID, BirthDate, Sex, Source, etc).

    I have two questions:

    1) I have code from another project that I use for any situation, but I'm curious if there's a more efficient coding that I could use specifically for a table
    Universal code:
    Code:
    Case .value Like "*SEX*"
      For Each cell In Intersect(hdrCell.EntireColumn, rngUsed).Offset(1, 0).Resize(rngUsed.Rows.Count - 1)
        ' ~~ apply data validation ~~
    2) after applying the data validation, I'd like each new row to "inherit" the column data validation (critical):
    A) without having to re-run the macro and
    B) confine data validation to the table column only (not have entire column with data validation).

    Thoughts anyone?

    Thanks y'all.

    As an aside, is there any way to create a list of data validation ranges/criteria? Kind of like what you can do with F3 | Paste List for named ranges/tables or what Jeff Weir did here (Easy way to back up CF formats)??

  2. #2
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    3,521
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Data validation - applying & listing via VBA

    I when data validation is applied to a table subsequent rows added to that table will have inherit that validation, but if multiple rows are added (pasted) to the table at once, the data validation exists, but does not change any pasted data, it is applied only if changes are made to the pasted data.

    Code:
    Very crude code.  Review Validation Object in VBA help to expand
    
    Sub DocumentCellValidation()
    
        Dim rngCell As Range
        Dim x
        
        On Error Resume Next
        For Each rngCell In ActiveSheet.UsedRange.Cells
            x = rngCell.Validation
            If Err.Number <> 0 Then Debug.Print rngCell.Address, rngCell.Validation.Type
            Err.Clear
        Next
        
    End Sub
    Phil

    - Use CODE tags to keep your code formatted. See: BB Tags
    - How to attach Screenshots
    - Try searching for your answer first, see how
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

  3. #3
    Board Regular Dr. Demento's Avatar
    Join Date
    Nov 2010
    Location
    Skipping stones off Charon's Ferry
    Posts
    504
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data validation - applying & listing via VBA

    Thanks so much! That's too easy (when someone else provides the answer

    However, with applying validation to new rows -- since each column has different validation (mostly from lists), would I use OFFSET to "copy" the validation from the cell above and apply it, column by column, to the new row??
    Last edited by Dr. Demento; Feb 9th, 2016 at 10:22 PM.

  4. #4
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    3,521
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Data validation - applying & listing via VBA

    When you create a table and establish (possibly different) validation in each cell in the first row, each column's unique validation is carried down that column as the table is expanded. Tested with Excel 2010.
    Phil

    - Use CODE tags to keep your code formatted. See: BB Tags
    - How to attach Screenshots
    - Try searching for your answer first, see how
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

  5. #5
    Board Regular Dr. Demento's Avatar
    Join Date
    Nov 2010
    Location
    Skipping stones off Charon's Ferry
    Posts
    504
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data validation - applying & listing via VBA

    So by adding a row to the bottom of the table and then pasting the data in the new, empty row, it will automatically inherit the validation for each column? Cool!

    Thanks so much.

Some videos you may like

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
  •