Setting Data Validation Rules in Excel

Posted on 2011-04-19
Last Modified: 2012-05-11
How do I set a data validation rule for column TRIP CODE in the attached spreadsheet that will only allow the data entry clerks to enter a four digit year, a dash, and a two digit numeric code, so they must enter it in a format like, 2011-01 or 2010-99.

See attached.

Question by:Carlynne
    LVL 30

    Accepted Solution

    Ok I have added the code in the Excel file so that the user has to add in "####-##" format only.

    I have added it with the other code which I suggested in your other thread.

    Please see sample attached.


    Private Sub Worksheet_Change(ByVal Target As Range)
        Application.EnableEvents = False
        On Error GoTo Whoa
        If Not Intersect(Target, Columns(15)) Is Nothing Then
            If Target.Offset(, -3).Value < 1 Or Target.Offset(, -3).Value > 5 Then
                MsgBox "Age > 5"
            End If
        ElseIf Not Intersect(Target, Columns(2)) Is Nothing Then
            If Not Target.Value Like "####-##" Then
                MsgBox "Invalid Format"
            End If
        End If
        Application.EnableEvents = True
        Exit Sub
        MsgBox Err.Description
        Resume letscontinue
    End Sub

    Open in new window


    Author Closing Comment

    Many thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now