Date format and text on Textbox

YingFa

Board Regular
Joined
Nov 4, 2019
Messages
63
Hello,

I have this code that prevents the user to enter the incorrect date format. I am wondering if it is possible for the textbox to accept "N/A". Meaning that if the user enters N/A to textbox19 the message box still shows "Date format is not correct" and the back color as yellow but it allows the user to keep using the form (as if Cancel = False) but only when "N/A" is entered on textbox19. When the date is not entered in the specified format on the code then it should work the same all the same, showing the message "Date format is not correct", the back color in yellow and not allowing to keep using the form (Cancel = True). Can I please have your help with this? Thank you.

VBA Code:
Private Sub TextBox19_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    With TextBox19
        If IsDate(.Text) Then
            .Text = Format(DateValue(.Text), "mm/dd/yyyy")
            .BackColor = vbWhite
        Else
            MsgBox "Date format is not correct"
            .BackColor = vbYellow
            Cancel = True
        End If
    End With
End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi,
try this update to your code & see if does what you want

VBA Code:
Private Sub TextBox19_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    With TextBox19
        If IsDate(.Text) Then
            .Text = Format(DateValue(.Text), "mm/dd/yyyy")
            .BackColor = vbWhite
        Else
            MsgBox "Date format is not correct"
            .BackColor = vbYellow
            Cancel = CBool(UCase(.Text) <> "N/A")
        End If
    End With
End Sub

Dave
 
Upvote 0
Thank you for your help with this. I tried it and it seems to work. I will keep trying it over the next days but I'm pretty sure it will work fine.
The only thing is if I click on textbox19 and I leave it blank, then the message will popping up once and again and it will not allow me to close or cancel the form to close it. That was previously happening with the first code. So, if the user clicks on textbox19 by error, meaning it was not his/her intention to enter data on it. Then, the code does not allow to keep on filling the other elements of the form. Is it possible to modify the code to include that or by nature it will not be avoided?
 
Upvote 0
Did you manage to solve your issues
Ain issues I have with dates is sometimes they appear as American format mm DD yy, even when I format as DD mmm yy
 
Upvote 0
Did you manage to solve your issues
Ain issues I have with dates is sometimes they appear as American format mm DD yy, even when I format as DD mmm yy
Hi, thank you for following up on this. The code you provided works. I will keep it. Basically, if I want to submit the form and the date format is incorrect it will not allow me. It will only allow me to submit when I type the write date or N/A. Thank you very much!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top