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

Restrict date format for UserForm
 

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

Restrict date format for UserForm
 

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" wrote in message
...
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