Custom Data Validation

superman_86 · #1 11-11-2008, 03:32 PM

I have a question which seems relatively simple around Data Validation.

Column A contains a free text field for a date. Column B, contains a list Data Validation of status. The status drop down options are "Complete","In Process","Cancelled".

The goal is whenever someone selects "Complete" from the status column if there is not a date in column A to have an error message pop up. Can someone please assist? Thank you! (Note this is in Excel 2007)

A B
Date Status

**pjoaquin** · #2 11-11-2008, 04:47 PM

Hi Superman,

I don't believe you could do both with Data Validation (create the drop-down list AND check to see if a date is in column A of that row). For that you could use a worksheet_change even macro like the one below:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 2 Or Target.Value <> "Complete" Then Exit Sub
    If Not (IsDate(Cells(Target.Row, 1).Value)) Then
        MsgBox "You must first enter a date in column A before " & _
            "changing the status!", vbExclamation, "Warning!"
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
    End If
End Sub

Any time you change a value in column B to Complete it will check to see if a valid date exists in column A for that row. If not, the user will get a warning message and the last change will be undone. If yes, the user won't see any message (it will allow them to change the status).

Hope that helps!

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)