Re: Userform Date Formatting MM/DD/YYYY

From: Bob Phillips (bob.phillips_at_notheretiscali.co.uk)
Date: 01/05/05 

Date: Wed, 5 Jan 2005 09:20:34 -0000

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