Results 1 to 10 of 10

Thread: Help required developing a Date error message

  1. #1
    Seeker Graeme Smith's Avatar
    Join Date
    Jul 2013
    Location
    Canberra Australia
    Posts
    9
    Articles
    0

    Help required developing a Date error message



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am developing a VB Userform which includes a start date (Date referral received) and end date (Case resolved date). I am wanting to develop a message box that illustrates a message when the user has put in an end date which is earlier than the start date e.g. "The Case Resolved Date cannot be before the Date Referral Received Date - please review the Case Resolved Date.

    Can someone please assist me with some suggested code.

    thanks

  2. #2
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    Try this
    Code:
    Option Explicit
    
    
    
    
    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        If Not IsDate(Me.TextBox1.Value) Then
            MsgBox "Please enter a valid date"
            Me.TextBox1.Value = Empty
        End If
    End Sub
    Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        With Me
            If Not IsDate(.TextBox2.Value) Then
                MsgBox "Please enter a valid date"
                .TextBox2.Value = Empty
            ElseIf CDate(.TextBox2.Value) < .TextBox1.Value Then
                MsgBox "End date cannot be earlier than the start date"
                .TextBox2.Value = Empty
            End If
        End With
    End Sub
    Hope that helps

    Roy

  3. #3
    Seeker Graeme Smith's Avatar
    Join Date
    Jul 2013
    Location
    Canberra Australia
    Posts
    9
    Articles
    0
    Hi Roy,

    Thank you for your assistance.

    Hopefully you might be able to address a further issue. I inserted the code you provided then I got an error on another piece of code 'Compile error: Ambiguous name detected:tbx8_Exit. As you can see in the code below there are three messages associated with date fields, two are regarding the date format and the other is the piece of code you greatfully sent me regarding end date before start date. I have highlighted the bit of code that gets highlighted with the error.

    Also, when I entered your code and ran the userform it doesn't allow me the opportunity to change the date, instead it just closes the userform.

    I would be in your debt in you can assist.



    'Checks that the user has entered the correct date format for text box 1
    Private Sub tbx1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim strDate As String
    strDate = tbx1.Text
    If Not IsDate(strDate) Then
    MsgBox "Your date format is incorrect" & vbCrLf & "Enter a date as dd/mm/yyyy", _
    vbOKOnly + vbExclamation
    Cancel = True
    End If
    End Sub

    'Checks that the user hasn't entered a Case Resolved Date with an earlier vale than Date Referral received
    Private Sub tbx8_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me
    If Not IsDate(.tbx8.Value) Then
    MsgBox "Please enter a valid date"
    .tbx8.Value = Empty
    ElseIf CDate(.tbx8.Value) < .tbx1.Value Then
    MsgBox "Case Resolved Date cannot be earlier than Referral Received Date"
    .tbx8.Value = Empty
    End If
    End With
    End Sub

    'Checks that the user has entered the correct date format for text box 8
    Private Sub tbx8_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim strDate As String
    strDate = tbx8.Text
    If Not IsDate(strDate) Then
    MsgBox "Your date format is incorrect" & vbCrLf & "Enter a date as dd/mm/yyyy", _
    vbOKOnly + vbExclamation
    Cancel = True
    End If

    End Sub

  4. #4
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    You cannot have two identical events for the same control, in this case you have two of these

    Private Sub tbx8_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Hope that helps

    Roy

  5. #5
    Seeker Graeme Smith's Avatar
    Join Date
    Jul 2013
    Location
    Canberra Australia
    Posts
    9
    Articles
    0
    Roy,

    So what your saying is that I can either have a date format error message OR an end date before start date error message BUT not both?

  6. #6
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    No, I'm saying that you cannot have two of the same events for one control. You just need to combine the two as mine actually does.
    Hope that helps

    Roy

  7. #7
    Seeker Graeme Smith's Avatar
    Join Date
    Jul 2013
    Location
    Canberra Australia
    Posts
    9
    Articles
    0
    Hi Roy

    Thanks for all your help. Please excuse my lack of VBA knowledge - I'm in the beginners league!

    Can I ask you one more question without pushing the friendship. I entered the code you provided me that works well, however, the userform unloaded before I had the opportunity to enter a valid date. How do I prevent this from occuring?

    regards
    Graeme

  8. #8
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    Your code must unload the form. Have you added Unload Me to the code?
    Hope that helps

    Roy

  9. #9
    Seeker Graeme Smith's Avatar
    Join Date
    Jul 2013
    Location
    Canberra Australia
    Posts
    9
    Articles
    0
    Hi Roy,


    This is part of the code that I have:

    'Checks that the Case resolve date is not before the Date referral received
    Private Sub tbx8_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me
    If Not IsDate(.tbx8.Value) Then
    MsgBox "Please enter a valid date"
    .tbx8.Value = Empty
    ElseIf CDate(.tbx8.Value) < .tbx1.Value Then
    MsgBox "Case resolve date cannot be earlier than Date referral received"
    .tbx8.Value = Empty
    End If
    End With

    'Closes form
    Unload Me
    End Sub

    'Checks that the user has entered the correct date format
    Private Sub tbx1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim strDate As String
    strDate = tbx1.Text
    If Not IsDate(strDate) Then
    MsgBox "Your date format is incorrect" & vbCrLf & "Enter a date as dd/mm/yyyy", _
    vbOKOnly + vbExclamation
    Cancel = True
    End If

    End Sub

    thanks for your help
    regards
    Graeme

  10. #10
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    As I said, your code closes the UserForm

    Code:
    'Closes form
    Unload Me
    End Sub
    Hope that helps

    Roy

Posting Permissions

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