Validate TextBox Date Entry For Year

  • In my form I have the user enter in the current date in Textbox1. My program is designed only to work in 2009 so I want to check to make sure the 1) the date is in 2009 and 2) textbox1 is not empty. If it is empty then it displays a message box with "Not a Valid Date. Please Enter Date as MM/DD/YYYY. Date has to be in 2009" - this doesn't work. Second, if the date is outside of 2009 it is to display a meeage box saying "Date has to be in 2009".


    Here is my current code which is not working.



    Any help is greatly appreciated.


    Guy

  • Re: Validate Textbox Entry Is Between Dates


    User entering dates the CORRECT way is more likely to fail, than pass. Disable the TextBox and have a button to launch a Calendar Control on a 2nd UserForm. In the Intialize Event of the 2nd UserForm use code like;

    Code
    Me.Calendar1.Year=2009
  • Re: Validate TextBox Date Entry For Year


    Dave, thank you for your reply. I followed the instructions to add Calendar Control (http://www.ozgrid.com/VBA/excel-calendar-dates.htm) but there isn't an option for the Calendar (Calendar Control 10.0). There is nothing that says Calendar. I use Excel 2003 for PC. What am I missing?


    Also, this spreadsheet is being distributed to MANY users. Will it work for everyone or just folks who have that control installed on their system?


    Thanks,


    Dan

  • Re: Validate TextBox Date Entry For Year


    If its going to be used on other PC's best skip the Calendar Control and use this code;

  • Re: Validate TextBox Date Entry For Year


    Hi Dan,


    Here's 2 versions. The first uses a Constant of "/2009' so the user doesn't have to enter the Year. The second requires the year to be entered.


    Both ask the user if the date is correct. I don't see how you can avoid that as user could enter a valid date in the wrong format...


    [vba]


    Option Explicit
    Const yr = "/2009" 'Needed for First example only
    Public LeaveMe As Boolean


    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim edate As Long


    '
    ' Example 1
    '


    ' User closed form
    If LeaveMe = True Then Exit Sub

    ' Was blank
    If TextBox1 = vbNullString Then
    MsgBox "Please Enter Date as MM/DD. Year will be 2009."
    Cancel = True
    Exit Sub
    ' Not a date
    ElseIf Not IsDate(TextBox1 & yr) Then
    MsgBox "Not a Valid Date."
    TextBox1 = vbNullString
    Cancel = True
    Exit Sub
    End If

    ' Confirm with user
    edate = MsgBox(Format(CDate(TextBox1 & yr), "mmm, dd, yyyy") & vbCrLf & "Is this the correct date?", vbYesNo + vbQuestion)


    'Yes = 6
    If edate <> 6 Then Cancel = True


    End Sub


    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim edate As Long


    '
    ' Example 2
    '



    ' User closed form
    If LeaveMe = True Then Exit Sub

    ' Was blank
    If TextBox1 = vbNullString Then
    MsgBox "Please Enter Date as MM/DD/YYYY. Date has to be in 2009."
    Cancel = True
    Exit Sub
    ' Check year
    ElseIf IsDate(TextBox1) Then
    If Not Year(TextBox1) = 2009 Then
    MsgBox "Year of date must be 2009"
    Cancel = True
    Exit Sub
    End If
    Else
    ' Not a date
    MsgBox "Not a Valid Date."
    TextBox1 = vbNullString
    Cancel = True
    Exit Sub
    End If

    ' Confirm with user
    edate = MsgBox(Format(CDate(TextBox1), "mmm, dd, yyyy") & vbCrLf & "Is this the correct date?", vbYesNo + vbQuestion)


    'Yes = 6
    If edate <> 6 Then Cancel = True

    End Sub


    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    LeaveMe = True
    End Sub


    [/vba]

  • Re: Validate TextBox Date Entry For Year


    Quote

    I don't see how you can avoid that as user could enter a valid date in the wrong format...

    That shouldn't matter as the date format of a valid date is only visual.

  • Re: Validate TextBox Date Entry For Year


    I was thinking that if the user entered D/M/Y that the msgbox would give them a chance to see what Excel thinks they entered. 12/1/2009 and 1/12/2009 for instance.

  • Re: Validate TextBox Date Entry For Year


    I would use the DateSerial Function to convert the date at the needed time. E.g


    Code
    dDate=TextBox1
    dDate=DateSerial(Year(dDate),Month(dDate),Day(dDate))

    Never trust the users :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!