Restrict date format for UserForm

I was given the following code some time ago (with some amendments by me),
to restrict the entry in a UserForm. I find that I can enter a date like
31/11/05 (which is not a correct date) but it is still excepted by the code,
(except that it transposes is to 5/11/31).

I would like, if possible, to only allow correct dates to be entered, and
then only in the d/m/yy format.
Can someone please reconstruct to achieve this?

Rob

Private Sub TextName1_KeyPress(ByVal _
KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57
Case Else
KeyAscii = 0
End Select
End Sub

Private Sub TextName1_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
If Shift = 2 Then KeyCode = 0
End Sub
Private Sub CancelButton_Click()
Unload Me
End Sub

Private Sub OKButton_Click()
Dim D As Date
'Make sure a date is entered
If IsDate(TextName2.Text) Then
D = DateValue(TextName2.Text)
Else
MsgBox "You must enter a date."
'to clear wrong entry and reset form
Unload Me
EnterDate.Show
Exit Sub
End If
'Transfer the date
Range("G1") = D
Unload Me
End Sub

Have you thought about using a calendar control to make life (yours and the
user's) easier?

Ron de Bruin has some tips/links at:
http://www.rondebruin.nl/calendar.htm

Or even using 3 different controls (comboboxes/listboxes/spinners and labels) to
get the date?



Rob wrote:
>
> I was given the following code some time ago (with some amendments by me),
> to restrict the entry in a UserForm. I find that I can enter a date like
> 31/11/05 (which is not a correct date) but it is still excepted by the code,
> (except that it transposes is to 5/11/31).
>
> I would like, if possible, to only allow correct dates to be entered, and
> then only in the d/m/yy format.
> Can someone please reconstruct to achieve this?
>
> Rob
>
> Private Sub TextName1_KeyPress(ByVal _
> KeyAscii As MSForms.ReturnInteger)
> Select Case KeyAscii
> Case 48 To 57
> Case Else
> KeyAscii = 0
> End Select
> End Sub
>
> Private Sub TextName1_KeyDown(ByVal _
> KeyCode As MSForms.ReturnInteger, _
> ByVal Shift As Integer)
> If Shift = 2 Then KeyCode = 0
> End Sub
> Private Sub CancelButton_Click()
> Unload Me
> End Sub
>
> Private Sub OKButton_Click()
> Dim D As Date
> 'Make sure a date is entered
> If IsDate(TextName2.Text) Then
> D = DateValue(TextName2.Text)
> Else
> MsgBox "You must enter a date."
> 'to clear wrong entry and reset form
> Unload Me
> EnterDate.Show
> Exit Sub
> End If
> 'Transfer the date
> Range("G1") = D
> Unload Me
> End Sub

--

Dave Peterson

Thanks Dave.

There always seems a better way to tackle something in Excel. I'll certainly
have a go at that alternative.

Rob

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Have you thought about using a calendar control to make life (yours and
> the
> user's) easier?
>
> Ron de Bruin has some tips/links at:
> http://www.rondebruin.nl/calendar.htm
>
> Or even using 3 different controls (comboboxes/listboxes/spinners and
> labels) to
> get the date?
>
>
>
> Rob wrote:
>>
>> I was given the following code some time ago (with some amendments by
>> me),
>> to restrict the entry in a UserForm. I find that I can enter a date like
>> 31/11/05 (which is not a correct date) but it is still excepted by the
>> code,
>> (except that it transposes is to 5/11/31).
>>
>> I would like, if possible, to only allow correct dates to be entered, and
>> then only in the d/m/yy format.
>> Can someone please reconstruct to achieve this?
>>
>> Rob
>>
>> Private Sub TextName1_KeyPress(ByVal _
>> KeyAscii As MSForms.ReturnInteger)
>> Select Case KeyAscii
>> Case 48 To 57
>> Case Else
>> KeyAscii = 0
>> End Select
>> End Sub
>>
>> Private Sub TextName1_KeyDown(ByVal _
>> KeyCode As MSForms.ReturnInteger, _
>> ByVal Shift As Integer)
>> If Shift = 2 Then KeyCode = 0
>> End Sub
>> Private Sub CancelButton_Click()
>> Unload Me
>> End Sub
>>
>> Private Sub OKButton_Click()
>> Dim D As Date
>> 'Make sure a date is entered
>> If IsDate(TextName2.Text) Then
>> D = DateValue(TextName2.Text)
>> Else
>> MsgBox "You must enter a date."
>> 'to clear wrong entry and reset form
>> Unload Me
>> EnterDate.Show
>> Exit Sub
>> End If
>> 'Transfer the date
>> Range("G1") = D
>> Unload Me
>> End Sub
>
> --
>
> Dave Peterson