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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,377
Office Version
  1. 2019
Platform
  1. Windows
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
 

YingFa

Board Regular
Joined
Nov 4, 2019
Messages
63
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?
 

rhombus4

Active Member
Joined
May 26, 2010
Messages
341
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

YingFa

Board Regular
Joined
Nov 4, 2019
Messages
63
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,755
Messages
5,597,929
Members
414,193
Latest member
bb60

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
Top