Date validation code

[paulyf]

Hi all,

I've got three text boxes on a form, entitled "day", "month" and "year". I've managed to concactenate them and format them into a date that I can plug into Excel. However, there is nothing to stop the user selecting an invalid date, e.g. 31st April 2001.

Does anybody have example code to allow only valid dates to be entered????

Many Thanks in anticipation.

Cheers - Paul

 

[Mark O'Brien]

One method I've used, is to use comboboxes instead of text boxes. Combos are a pain in the **** for the user, but can be a programmer's delight.

However, to answer your question, you're going to have to create validation code yourself, but here's a format I suggest (I've assumed that your Month textbox is called txtMonth and the Day textbox is called txtDay):

<pre>
Select Case txtMonth.Text

Case 1, 3, 5, 7, 8, 10, 12 '31 day months
If txtDay.Text > 31 Then
MsgBox "There aren't that many days!"
End If
Case 4, 6, 9, 11
If txtDay.Text > 30 Then '30 day months
MsgBox "There aren't that many days!"
End If
Case Else 'February
'You're going to want to check for a leap year here.

End Select</pre>

Note:: I've used numbers for the months, you can put in the words or whatever instead. (just remember if you're using text use double quotes around them)

I hope this is a good start for you.

 

[paulyf]

Thanks A Lot Mark - That's Great!

I've stumbled upon the IsDate function - does that have any limitations?

Cheers - Paul

 

[Aladin Akyurek]

Just a thought.

You could ask the user to select the month first, then the year, finally the day.

This order of selection would allow you to dynamically construct the list of the day numbers to be shown.

The formula that follows would produce such a list. Put it in a cell in the first row where the other lists are:

=IF(MONTH(DATE($B$1,$A$1,ROW()))=$A$1,DAY(DATE($B$1,$A$1,ROW())),"")

to be copied down till row 31.

A1 is where the month is selected and B1 is where the year is selected.

You need to name this range using an OFFSET formula. You can then use the name so created as source list.

Aladin
This message was edited by Aladin Akyurek on 2002-05-02 09:28
This message was edited by Aladin Akyurek on 2002-05-02 09:29

 

[Mark O'Brien]

On 2002-05-02 08:46, paulyf wrote:
Thanks A Lot Mark - That's Great!

I've stumbled upon the IsDate function - does that have any limitations?

Cheers - Paul

The only limitation that's immediately obvious to me is that it wont tell you what part of the date is wrong. (since it's a boolean check) Apart from that, there's no harm in using "IsDate".

 

[qmp]

I have the same thing in my budget progam. I use the validation box.

After you set your range using setting click on the input message tab and put a message with your dropdown box, which you don't have to show. Just unclick "Show input message when cell is selected"

Then select the error alert tab and put a message here also. Like the input message you can unshow this also.

These will not let you to put anything in the cell unless its in the dropdown list.... hope this helps.......... qmp