Userform Date Formatting MM/DD/YYYY

[Mike]

Thank you in advance for your help.

I have a UserForm with the following code:

ActiveCell.Offset(0, 1).Select
Selection.NumberFormat = "MM/DD/YYYY"
ActiveCell = UserForm1.TextBox2

The user is entering a date into Textbox2. I want to make
it so that they can only enter the date in MM/DD/YYYY
format. The above code doesn't realy work.

So far I have tried the following:

1. I went into Control Panel -> Regional Options and set
the short date format as MM/DD/YYYY. This updates the
dates when I type them directly into Excel but not when I
enter dates in the UserForm and then have the UserForm
place the dates on the worksheet.

2. I also tried the following code from another Newsgroup:
Private Sub TextBox2_KeyPress(ByVal KeyAscii As _
MSForms.ReturnInteger)
Dim cDelim As Long
cDelim = Len(TextBox2.Text) - Len(Replace
(TextBox2.Text, "/", ""))
Select Case KeyAscii
Case Asc("0") To Asc("9"): 'OK
Case Asc("/"):
If cDelim = 2 Then
KeyAscii = 0
Else
cDelim = cDelim + 1
End If
Case Else: KeyAscii = 0
End Select
End Sub

This hasn't solved my problem either. Maybe I'm not
placing this code in the correct part of the Userform? I
am using Windows 2000 Professional and Excel 2000. Thanks
again for your help.

 

[Bob Phillips]

Are you sure that your textbox is named Texbox2 as in the code sample?

It is very difficult to create a software date mask (which is what you
want), due to the number of combinations. The best thing is to trap the
input, as my routine does, and then validate at the end that it is a valid
date.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Mike]

I just double checked and my textbox is definitely named
TextBox2. I think one alternative would be to have them
input the month in one list box, the day in another list
box, and the year into a third list box. I could then
concantenate the output all into one cell. However this
would require three seperate list boxes and to me that
seems cumbersome.

 

[Bob Phillips]

Mike,

One way to this would be to have spinbuttons and 4 textboxes all linked. It
requires more code, but it is more resilient.

Do you want some code for it?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Bob -

Sure I'd appreciate it very much if you would give me
some code for those spin buttons.

Are you sure there isn't some way I could get that
software date mask to work? Also I found a third
alternative. I could create a calendar button in VB and
get the user to select the date from that. You probably
already know how to do this but if you want the code for
the calendar button from me I'll be more than happy to
post it. Thanks again,

Mike

 

[Bob Phillips]

Mike,

The date mask is tricky, and I have not found a successful implementation
(believe me I have tried. I have done the same in JavaScript, VBScript, et
al, never to my full satisfaction).

The problem with the calendar is you use a control, and you have to ship
this with your application in case the recipient doesn't have it (not even
sure about the license implications), so I avoid it.

Anyway, here is that spinner code.

'|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|||||||||||||||||||||||||||||||||||||||||||||||||||

This technique uses spinbuttons to control the date input.

There are 3 textboxes, one for the month, one for the day, and one for the
year. Each has a spinbutton associated with it. These controls are called
txtMonth
txtDay
txtYear
spnMonth
spnDay
spnYear.
And finally, there is another text box in which the full date is output,
this being called txtdate.

Create these controls on a userform, with the appropriate textboxes and
spinbuttons adjacent.

In the example, the month in the month textbox is shown as a month name
(short form, e.g. Sep).

If an invalid choice is made, such as trying to increment the day number
when the current date is 28th Feb 2005, the code won't allow it, no warnings
or errors, it just doesn't do it. This applies to months (e.g. you can't
increment the month if the current date is 31st March 2004, you need to
decrement the day first), and years (e.g. You can't decrement the year if
the current date is 29th Feb 2004, you must change the month or day first).

The code also initialises the form with today's date.

Option Explicit

Dim aryMonths
Dim fEvents As Boolean
Const FormatMask As String = "mm/dd/yyyy"

Private Sub spnDay_Change()
If Not fEvents Then
fEvents = True
FormatDate Me.spnDay
fEvents = False
End If
End Sub

Private Sub spnMonth_Change()
If Not fEvents Then
fEvents = True
FormatDate Me.spnMonth
fEvents = False
End If
End Sub

Private Sub spnMonth_SpinDown()
With Me
.txtMonth.Text = aryMonths(.spnMonth.Value)
End With
End Sub

Private Sub spnMonth_SpinUp()
With Me
.txtMonth.Text = aryMonths(.spnMonth.Value)
End With
End Sub

Private Sub spnYear_Change()
If Not fEvents Then
fEvents = True
FormatDate Me.spnYear
fEvents = False
End If
End Sub

Private Sub UserForm_Initialize()

aryMonths = Array("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", _
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

With Me
fEvents = True
With .spnMonth
.Min = 1: .Max = 12: .Value = Month(Date)
End With
With .spnDay
.Min = 1: .Max = 31: .Value = Day(Date)
End With
With .spnYear
.Min = 1900: .Max = 2999: .Value = Year(Date)
End With
fEvents = False
FormatDate .spnDay
End With

End Sub

Private Sub FormatDate(spinner As MSForms.SpinButton)
Dim nextDate As Date
With Me
.txtMonth.Text = aryMonths(.spnMonth.Value)
.txtDay.Text = Format(.spnDay.Value, "00")
.txtYear.Text = Format(.spnYear.Value, "0000")
.txtDate.Text = Format(.spnMonth.Value, "00") & "/" & _
Format(.spnDay.Value, "00") & "/" & _
.spnYear.Value

On Error Resume Next
nextDate = DateValue(.txtDate.Text)
On Error GoTo 0
If nextDate = 0 Then
fEvents = False
spinner.Value = spinner.Value - 1
End If
End With
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Mike]

Bob,

Thank you very much for that code. It was a big help.

Mike J

-----Original Message-----
Mike,

The date mask is tricky, and I have not found a successful implementation
(believe me I have tried. I have done the same in JavaScript, VBScript, et
al, never to my full satisfaction).

The problem with the calendar is you use a control, and you have to ship
this with your application in case the recipient doesn't have it (not even
sure about the license implications), so I avoid it.

Anyway, here is that spinner code.

'|||||||||||||||||||||||||||||||||||||||||||||||||||||||| |||||||||||||||||||
|||||||||||||||||||||||||||||||||||||||||||||||||||

This technique uses spinbuttons to control the date input.

There are 3 textboxes, one for the month, one for the day, and one for the
year. Each has a spinbutton associated with it. These controls are called
txtMonth
txtDay
txtYear
spnMonth
spnDay
spnYear.
And finally, there is another text box in which the full date is output,
this being called txtdate.

Create these controls on a userform, with the appropriate textboxes and
spinbuttons adjacent.

In the example, the month in the month textbox is shown as a month name
(short form, e.g. Sep).

If an invalid choice is made, such as trying to increment the day number
when the current date is 28th Feb 2005, the code won't allow it, no warnings
or errors, it just doesn't do it. This applies to months (e.g. you can't
increment the month if the current date is 31st March 2004, you need to
decrement the day first), and years (e.g. You can't decrement the year if
the current date is 29th Feb 2004, you must change the month or day first).

The code also initialises the form with today's date.

Option Explicit

Dim aryMonths
Dim fEvents As Boolean
Const FormatMask As String = "mm/dd/yyyy"

Private Sub spnDay_Change()
If Not fEvents Then
fEvents = True
FormatDate Me.spnDay
fEvents = False
End If
End Sub

Private Sub spnMonth_Change()
If Not fEvents Then
fEvents = True
FormatDate Me.spnMonth
fEvents = False
End If
End Sub

Private Sub spnMonth_SpinDown()
With Me
.txtMonth.Text = aryMonths(.spnMonth.Value)
End With
End Sub

Private Sub spnMonth_SpinUp()
With Me
.txtMonth.Text = aryMonths(.spnMonth.Value)
End With
End Sub

Private Sub spnYear_Change()
If Not fEvents Then
fEvents = True
FormatDate Me.spnYear
fEvents = False
End If
End Sub

Private Sub UserForm_Initialize()

aryMonths = Array

("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", _

 

[Guest]

Thanks for your help Bob. I think I'm going to use those
spin buttons.

-----Original Message-----
Mike,

The date mask is tricky, and I have not found a successful implementation
(believe me I have tried. I have done the same in JavaScript, VBScript, et
al, never to my full satisfaction).

The problem with the calendar is you use a control, and you have to ship
this with your application in case the recipient doesn't have it (not even
sure about the license implications), so I avoid it.

Anyway, here is that spinner code.

'||||||||||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||||||||
|||||||||||||||||||||||||||||||||||||||||||||||||||

This technique uses spinbuttons to control the date input.

There are 3 textboxes, one for the month, one for the day, and one for the
year. Each has a spinbutton associated with it. These controls are called
txtMonth
txtDay
txtYear
spnMonth
spnDay
spnYear.
And finally, there is another text box in which the full date is output,
this being called txtdate.

Create these controls on a userform, with the appropriate textboxes and
spinbuttons adjacent.

In the example, the month in the month textbox is shown as a month name
(short form, e.g. Sep).

If an invalid choice is made, such as trying to increment the day number
when the current date is 28th Feb 2005, the code won't allow it, no warnings
or errors, it just doesn't do it. This applies to months (e.g. you can't
increment the month if the current date is 31st March 2004, you need to
decrement the day first), and years (e.g. You can't decrement the year if
the current date is 29th Feb 2004, you must change the month or day first).

The code also initialises the form with today's date.

Option Explicit

Dim aryMonths
Dim fEvents As Boolean
Const FormatMask As String = "mm/dd/yyyy"

Private Sub spnDay_Change()
If Not fEvents Then
fEvents = True
FormatDate Me.spnDay
fEvents = False
End If
End Sub

Private Sub spnMonth_Change()
If Not fEvents Then
fEvents = True
FormatDate Me.spnMonth
fEvents = False
End If
End Sub

Private Sub spnMonth_SpinDown()
With Me
.txtMonth.Text = aryMonths(.spnMonth.Value)
End With
End Sub

Private Sub spnMonth_SpinUp()
With Me
.txtMonth.Text = aryMonths(.spnMonth.Value)
End With
End Sub

Private Sub spnYear_Change()
If Not fEvents Then
fEvents = True
FormatDate Me.spnYear
fEvents = False
End If
End Sub

Private Sub UserForm_Initialize()

aryMonths = Array

("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", _