Data Validation-Date & Text entry

[matyp77]

Hi,

First of all thank you for all your help.

In Excel 2003, I want to limit the data entered into a cell by either a date (preferably limiting that date also to a date after 4/1/09 for example) or certain text I.E. (apple, orange, lemon)

I cant figure out how to limit text selections and also allow for date entry instead.

HELP!!!

 

[lenze]

Maybe something like this?

=OR(AND(ISNUMBER(F1),F1>DATEVALUE("4/1/2009")),F1="Orange",F1="Apple")

lenze

 

[matyp77]

That works great! So i use that in a custom validation formula.

Now is there a way I can have the user select their option in a drop down box? (DATE ENTRY, Apple, orange)?

 

[lenze]

Can you use code? If so, create your dropdown list
Date,Orange, Apple, etc
Now, something like this?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Application.EnableEvents = False
If Target = "Date" Then
   rspn = InputBox("Enter a valid date")
   Target = rspn
End If
Application.EnableEvents = True
End Sub

lenze

 

[matyp77]

Ok, we're almost there. How about if i only want the code to apply to sheet 1, and the C column or the range C2:C34?!?

And is there also a way to limit the date entered to after 4/1/09?

I've tried myself and cant get it...

 

[lenze]

1. The code will only run in the sheet where it resides. So you place it in the Sheet1 module.

2. To specify a range of cells, replace

If Target.Address <> "$A$1" Then Exit Sub

with

If Intersect(Target, Range("$C$ 2:$C$34")) Is Nothing Then Exit Sub

3. Maybe this

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$C$ 2:$C$34")) Is Nothing Then Exit Sub
On Error GoTo Trap
Dim rspn As Variant
Application.EnableEvents = False
If Target = "Date" Then
   rspn = InputBox("Enter a valid date")
      If rspn > #4/1/2009# Then
          Target = rspn
      Else: MsgBox "Invalid Date"
   End If
End If
Trap: Application.EnableEvents = True
End Sub

May need to be tweaked a little
lenze

 

[matyp77]

I think we got it. Although it still allows a text entry...but good enough!

 

[MaxPayne13]

Hey, this is great, I know this post is pretty ancient but how about without code into a drop down list? The data validation formula works great but I don;t want to use code for my spreadsheet? I want the user to be able to enter a date or select a word from a list, the date does not have to be part of this list. Please help?