Re: Userform Date Formatting MM/DD/YYYY

anonymous_at_discussions.microsoft.com
Date: 01/07/05 

Date: Fri, 7 Jan 2005 09:46:01 -0800

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)
>
>
><anonymous@discussions.microsoft.com> wrote in message
>news:148c01c4f2bf$6ef594c0$a301280a@phx.gbl...
>> 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" <anonymous@discussions.microsoft.com> wrote in
>> message
>> >news:0ffd01c4f2aa$524a7530$a501280a@phx.gbl...
>> >> 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" <anonymous@discussions.microsoft.com> wrote
in
>> >> message
>> >> >news:052f01c4f291$2efbcc50$a601280a@phx.gbl...
>> >> >> 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 work***.
>> >> >>
>> >> >> 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.
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>
>.
>