Excel UserForm Textbox problem

[rob1963manwork]

Hello!

I manage a workbook with a userform. I have the code in the Save command button (below) which works as intended and is fully functional EXCEPT FOR ONE THING:

I need for the textbox called TXT_Date to reject any entry put into it unless it is an actual date

Users will be users and make mistakes and often i see a date as 01//01/2021 or just as 01/01 or as 01/01/20221.

My end goal is to prevent the userform from saving data unless this criteria is met. If it isn't met then I'd like to inform the user that this field must be corrected.

My question is What to do? and where in the code below to place it?

I feel like i'm so close, but yet so far!!





Private Sub COMM_BUTTON_SAVEINFO_Click()

'This section requires user to fill-in ALL text boxes OR the macro will not run and show the msgbox

If TXT_date.Value = "" Or TXT_AccountName.Value = "" Or TXT_DescriptiveText.Value = "" Or Not Not TXT_CreditAmount.Value = "" Then

MsgBox "You must complete all fields, some boxes are still empty! ", vbCritical
Exit Sub

Else

End If

'-----------------------------------------------------------------------------------------------------------

Dim LBpostingdate As String 'this is the name of the label(s)
LBpostingdate = TXT_date.Text 'this is the name of the label pointing at the text box next to it

Dim LBaccountname As String
LBaccountname = TXT_AccountName.Text

Dim LBdescriptivetext As String
LBdescriptivetext = TXT_DescriptiveText.Text

Dim LBcreditamount As Currency
LBcreditamount = TXT_CreditAmount.Text


Dim wsh As Worksheet
Set wsh = ThisWorkbook.Worksheets("backend")

Set tbl = wsh.ListObjects("table3")
Dim lRow As ListRow
Set lRow = tbl.ListRows.Add

With lRow

.Range(34) = LBpostingdate 'the .range(34) refers to the column number of the table where the information will be placed
.Range(41) = LBaccountname
.Range(56) = LBdescriptivetext
.Range(47) = LBcreditamount

MsgBox "Zero Pay entry yas been recorded successfully"



End With
End Sub

[CheeseSandwich]

How about something like:

Please Login or Register  to view this content.

[rorya]

01/01/20221 is a date though.

You can do some basic validation with IsDate but it is quite liberal in its interpretation (01/01 would be accepted for example.)

You might be better off using Day, Month and Year dropdowns.

[CheeseSandwich]

01/01/20221 is a date though.

You are right about the 01/01 bit but my system does not think that 01/01/20221 is a date.

[rob1963manwork]

How about something like:

Please Login or Register  to view this content.

Hi Cheese!!

I tried incorporating that into the IF statement of the code, but it was a no-go. I think it mainly has to do with the syntax of how to incorporate it that i am having trouble with. How about putting a textbox that will say "Hey dummy! fix the entry here!" LOL

[rob1963manwork]

01/01/20221 is a date though.

You can do some basic validation with IsDate but it is quite liberal in its interpretation (01/01 would be accepted for example.)

You might be better off using Day, Month and Year dropdowns.

Thanks Rorya!! However, then that would create a new question for me on how to concatenate the "01" and "01" and "2021" into 01/01/2021 that will place it in one cell at the destination sheet!

[CheeseSandwich]

Not that you will use it but you would implement it like below:

Please Login or Register  to view this content.

[rorya]

You'd use DateSerial(year, month, day) with the relevant control values for each argument.

[rorya]

You are right about the 01/01 bit but my system does not think that 01/01/20221 is a date.

It may be outside the computer's current range but it's still a date.

[CheeseSandwich]

It may be outside the computer's current range but it's still a date.

I understand that but IsDate currently throws it out and probably will for at least a century

[rob1963manwork]

Not that you will use it but you would implement it like below:

Please Login or Register  to view this content.

Cheese, I hate it when people thank users like yourself with "Thanks, Cheese.. you're a genius". But i'm afraid i'll have to say it..

"Thanks Cheese, you're a genius"

That worked perfectly! I just need to edit the "Dummy" part for real before my boss runs into it, although.. it would fit the bill! LOL

Thanks again,
Rob