How to validate both date (dd/mmm/yy) and text ("N/A") in Excel 2003

[Noob101]

Hi,

First of all thank you for all your help.

In Excel 2003, I have a spreadsheet where a number of people are going to be entering data, so I want to validate it. Specifically, I want them to only be able to enter a date (dd/mmm/yy) or "N/A". (Range BJ21:BJ450)

I can't figure out how to do this - help!

 

[JoeMo]

I think you will need VBA to do that. Here's some worksheet code you can try.
To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("BJ21:BJ450")) Is Nothing Then
    If Not IsDate(Target) Then
        If Target.Value <> "N/A" Then
            Application.EnableEvents = False
            MsgBox "Enter a date or N/A - no other entries allowed"
            Target.ClearContents
            Target.Select
            Application.EnableEvents = True
        End If
    End If
End If
End Sub

 

[Noob101]

Hi Thankyou so much,

One more thing, - what if I wanted to add another text option ('"N")?

THANKYOU!!!!

 

[JoeMo]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("BJ21:BJ450")) Is Nothing Then
    If Not IsDate(Target) Then
        If Target.Value <> "N/A" Or Target.Value <> "N" Then
            Application.EnableEvents = False
            MsgBox "Enter a date or N/A or N - no other entries allowed"
            Target.ClearContents
            Target.Select
            Application.EnableEvents = True
        End If
    End If
End If
End Sub

 

[Noob101]

Hiya,

Terribly sorry but this doesn't work. It reads it as "N/A or N", i.e if I type "N" a popup box says I can only enter a date or "N/A or N".

Thankyou!

 

[JoeMo]

Hiya,

Terribly sorry but this doesn't work. It reads it as "N/A or N", i.e if I type "N" a popup box says I can only enter a date or "N/A or N".

Thankyou!

Sorry that should be:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("BJ21:BJ450")) Is Nothing Then
    If Not IsDate(Target) Then
        If Target.Value <> "N/A" And Target.Value <> "N" Then
            Application.EnableEvents = False
            MsgBox "Enter a date or N/A or N - no other entries allowed"
            Target.ClearContents
            Target.Select
            Application.EnableEvents = True
        End If
    End If
End If
End Sub

 

[Noob101]

You are the man JoeMo!!

 

[Noob101]

Ok I swear this is the last question. I added some other columns and variables to the code and now if I try to copy and paste "N/A" down a column it comes up with "Run time error". Any thoughts on how to improve what I have done/fix this?

End Select

If Not Intersect(Target, Range("BJ21:BK450, BM21:BM450, BR21:BR450, BX21:BY450, CB21:CD450, CG21:CG450, CI21:CI450")) Is Nothing Then
If Not IsDate(Target) Then
If Target.Value <> "N/A" And Target.Value <> "N" And Target.Value <> "Exempt" And Target.Value <> "Enrolled" And Target.Value <> "Nominated" And Target.Value <> "Waitlisted" Then
Application.EnableEvents = False
MsgBox "Enter a date or N/A or N or Exempt or Enrolled or Waitlisted or Nominated - no other entries allowed"
Target.ClearContents
Target.Select
Application.EnableEvents = True
End If
End If
End If
End Sub