+ Reply to Thread
Results 1 to 3 of 3

Restrict date format for UserForm

  1. #1
    Rob
    Guest

    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



  2. #2
    Dave Peterson
    Guest

    Re: 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

  3. #3
    Rob
    Guest

    Re: 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" <petersod@verizonXSPAM.net> wrote in message
    news:43A439F1.562FB9A0@verizonXSPAM.net...
    > 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




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1