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" <[email protected]> wrote in message
news:[email protected]...
> 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.

[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.

>-----Original Message-----
>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" <[email protected]> wrote in
message
>news:[email protected]...
>> 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]

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)


"Mike" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> >-----Original Message-----
> >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" <[email protected]> wrote in
> message
> >news:[email protected]...
> >> 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 -

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

>-----Original Message-----
>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)
>
>
>"Mike" <[email protected]> wrote in
message
>news:[email protected]...
>> 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.
>>
>> >-----Original Message-----
>> >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" <[email protected]> wrote in
>> message
>> >news:[email protected]...
>> >> 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]

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)


<[email protected]> wrote in message
news:[email protected]...
> 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
>
> >-----Original Message-----
> >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)
> >
> >
> >"Mike" <[email protected]> wrote in
> message
> >news:[email protected]...
> >> 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.
> >>
> >> >-----Original Message-----
> >> >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" <[email protected]> wrote in
> >> message
> >> >news:[email protected]...
> >> >> 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.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

[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", _
> "Jul", "Aug", "Sep", "Oct", "No
v", "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)
>
>
><[email protected]> wrote in message
>news:[email protected]...
>> 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
>>
>> >-----Original Message-----
>> >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)
>> >
>> >
>> >"Mike" <[email protected]> wrote in
>> message
>> >news:[email protected]...
>> >> 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.
>> >>
>> >> >-----Original Message-----
>> >> >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" <[email protected]> wrote
in
>> >> message
>> >> >news:[email protected]...
>> >> >> 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.
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>
>.
>

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", _
> "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)
>
>
><[email protected]> wrote in message
>news:[email protected]...
>> 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
>>
>> >-----Original Message-----
>> >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)
>> >
>> >
>> >"Mike" <[email protected]> wrote in
>> message
>> >news:[email protected]...
>> >> 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.
>> >>
>> >> >-----Original Message-----
>> >> >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" <[email protected]> wrote
in
>> >> message
>> >> >news:[email protected]...
>> >> >> 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.
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>
>.
>

[mrxdm]

Thanks to all of you too that due to this discussion I also got a solution on this problem which I was facing since 2 years.