• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA userform date format with error check, yet allows user to skip entry

Hello all!
I have the following code for formatting textboxes as dates. I like it because if someone enters the wrong format, a message pops up and the textbox turns yellow.

However, if the user 'decides' to delete the 'bad' entry and leave the textbox blank, the code will not allow them to leave the textbox blank and move to the next field. It forces them to fill in a date.
How can the code be improved to allow the user the option to leave the date blank? Maybe add a message that says: "Are you sure you want to leave this field blank?" Yes/No and if they select "Yes" allow them to skip, "No" then it loops back to the message "Please enter a date. Ex. 12/31/2022".

Thank you in advance!

Code:
'***Date Format Stage Due***
Private Sub TextStageDue_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    With TextStageDue
        If IsDate(TextStageDue.Text) Then
            TextStageDue.Text = Format(DateValue(TextStageDue.Text), "mm/dd/yyyy")
            .BackColor = vbWhite
        Else
            MsgBox "Please enter a date. Ex. 12/31/2022"
            .BackColor = vbYellow
            Cancel = True
        End If
    End With
End Sub
 
Hello, just obviously testing the textbox content :​
Code:
Private Sub TextStageDue_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    With TextStageDue
        If IsDate(.Text) Then
            .BackColor = vbWhite
            .Text = CDate(.Text)
        ElseIf .Text > "" Then
            .BackColor = vbYellow
            MsgBox "Please enter a valid date."
            Cancel = True
        End If
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
I test it and we are almost there. I typed in a crazy number and the message popped up. I was able to delete the numbers I entered and I was able to move to the next field. However, I'm not sure I want the date field to remain yellow. Can that me fixed?

81252
 
You are awesome!!!! Here is what I did and it worked beautifully. Thank you so very, very much!

Code:
Private Sub TxtStageDue_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    With txtStageDue
        If IsDate(.Text) Then
            .BackColor = vbWhite
            .Text = CDate(.Text)
        ElseIf .Text > "" Then
            .BackColor = vbYellow
            MsgBox "Please enter a valid date."
            Cancel = True
        ElseIf .Text = "" Then
            .BackColor = vbWhite
        End If
    End With
End Sub
        End If
    End With
End Sub
 
As I wrote in my previous post according to Logic the last ElseIf should just be an Else codeline​
as the previous test check if the text is not empty (aka > "") so if not it's empty so = "" obviously ...​
 
Back
Top