Results 1 to 5 of 5

Macro with data validation

This is a discussion on Macro with data validation within the Excel Questions forums, part of the Question Forums category; Is it possible to create a macro performs data validation? We would like to allow users to capture data into ...

  1. #1
    Board Regular
    Join Date
    Oct 2002
    Posts
    108

    Default Macro with data validation

    Is it possible to create a macro performs data validation? We would like to allow users to capture data into a worksheet, then click a button that runs a macro to perform data validation on certain fields. For example, one field may be called User Name. We expect user names to appear as Johnny, Mike and Damian. The user enters Michael, so when the data validation macro runs, it highlights the cell that has Michael entered so they know it failed validation. Of course, we would need validation performed on 4 unique columns that contain different data each column with its own rules.

    Any suggestions are greatly appreciated.

    John

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default Re: Macro with data validation

    Why not use the builtin Data | Validation feature ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Oct 2002
    Posts
    108

    Default Re: Macro with data validation

    Hi Juan,
    I appreciate your response. We actually started down the path with the built-in validation but the user demanded the ability to copy/paste data and when they copy/paste within a cell that has validation, the validation does not function. Due to this issue we have decided to find a method for utilizing a macro that runs AFTER they enter their data.

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,657

    Default Re: Macro with data validation

    Here is a small macro that should help get you started. This checks the entries in column A for name, and highlights anything that in not Johnny, Mike, or Damian yellow.

    I am assuming the data begins on line 2, and we are using column A to determine where the last row falls.

    Code:
    Sub MyCheck()
    
        Application.ScreenUpdating = False
    
        Dim i As Integer
        
    '   Loop from row 2 to end (using column A to determine last row
        For i = 2 To Range("A65536").End(xlUp).Row
    '   Check value in column A for names
            Select Case Cells(i, 1).Value
                Case "Johnny", "Mike", "Damian"
    '   If column A is one of pre-defined choices, do NOT highlight cell
                    Cells(i, 1).Interior.ColorIndex = xlNone
    '   If column A is NOT one of pre-defined choices, highlight cell
                Case Else
                    Cells(i, 1).Interior.ColorIndex = 6
            End Select
        Next i
        
       Application.ScreenUpdating = True
        
    End Sub
    You can enter more Case Select statments (just fancy IF statements) inside the loop to handle the other columns (column 2 would be Cells(i,2)).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Apr 2012
    Posts
    1

    Cool Re: Macro with data validation

    I know that this example was posted a long time ago, but how would I go about changing it so as the list of valid values is extracted from a list of values that have been entered on a separate tab in the same workbook? Many thanks

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