Setting Data Validation Rules in Excel

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.

thanks,
carlynne
ERA-Reg-Template-2011.xlsx
CarlynneAsked:
Who is Participating?
 
SiddharthRoutCommented:
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.

Sid

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"
            Target.ClearContents
        End If
    ElseIf Not Intersect(Target, Columns(2)) Is Nothing Then
        If Not Target.Value Like "####-##" Then
            MsgBox "Invalid Format"
            Target.ClearContents
        End If
    End If
letscontinue:
    Application.EnableEvents = True
    
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume letscontinue
End Sub

Open in new window

ERA-Reg-Template-2011.xlsm
0
 
CarlynneAuthor Commented:
Many thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.