• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

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
0
Carlynne
Asked:
Carlynne
1 Solution
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now