Date and data validation not working

[Damian37]

Hello all,
I'm trying to add code to validate the format in which users are entering in a date within my userform, and I am also trying to validate if they enter in a specific value within a combobox, they will be unable to enter data within a textbox further down in the userform. Here is how my code is currently written, but neither my date or data validations are working. Any help is appreciated as I'm in a bit of a time crunch to get this to work. I have bolded the new coding I added to an existing code that was created by someone else at my job prior to it being given to me. Thank you all!

[code]
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim WS As Worksheet
Set WS = Worksheets("DetailOverShort")

'find first empty row in database
lRow = WS.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

ldate = Me.txtDate.Value

'check for a date
If Trim(Me.txtDate.Value) = "" Then
Me.txtDate.SetFocus
MsgBox "Please enter a Date"

ElseIf Trim(Me.txtDate.Value) <> "mm/dd/yyyy" Then
Me.txtDate.SetFocus
MsgBox "Please enter date in correct format"
Else
Me.txtDate.Value = ldate

End If

Exit Sub
'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected
With WS
.Unprotect Password:="password"
If MsgBox("Is all data correct?", vbYesNo) = vbNo Then

.Cells(lRow, 2).Value = Me.txtDate.Value
.Cells(lRow, 3).Value = Me.cboDirectIndirect.Value
.Cells(lRow, 4).Value = Me.txtPERNR.Value
.Cells(lRow, 5).Value = Me.txtName.Value
.Cells(lRow, 6).Value = Me.cboLocation.Value
.Cells(lRow, 10).Value = Me.txtAmt.Value
.Cells(lRow, 12).Value = Me.txtCorrection.Value
.Cells(lRow, 14).Value = Me.txtComments.Value
.Cells(lRow, 15).Value = Me.txtKioskNo.Value
.Cells(lRow, 16).Value = Me.txtKioskITDate.Value
.Cells(lRow, 17).Value = Me.txtKioskClosedDate.Value
.Cells(lRow, 18).Value = Me.txtKioskResolution.Value

' .Protect Password:="password"
End If

If cboDirectIndirect.Value = "Indirect" Then
txtAmt.Enabled = False
Else
txtAmt.Enabled = True
End If

Exit Sub
End With

'clear the data
Me.txtDate.Value = Format(Date, "Medium Date")
Me.cboDirectIndirect.Value = ""
Me.txtPERNR.Value = ""
Me.txtName.Value = ""
Me.cboLocation.Value = ""
Me.txtAmt.Value = ""
Me.txtCorrection.Value = ""
Me.txtComments.Value = ""
Me.txtKioskNo.Value = ""
Me.txtKioskITDate.Value = ""
Me.txtKioskClosedDate.Value = ""
Me.txtKioskResolution.Value = ""


End Sub

[nathansav]

ISDATE should help if ISDATE(X)

[Norie]

Try using IsDate to check if a valid date has been entered.

Please Login or Register  to view this content.

What's the problem here, the code looks fine to me?

Please Login or Register  to view this content.

[Damian37]

ISDATE should help if ISDATE(X)

Thanks nathansav,
Should I remove the ElseIf before? I added IsDate within my line of code, and I received an error:

Please Login or Register  to view this content.

[nathansav]

no that should be good, your logic was right, you were just trying to check the format, which could have been 99/99/9999. You could have done this way using Regular Expressions, but anyway...

Dates are numbers of days since 1901 i believe, they are just formatted nicely for you, so ISDATE will check that for you.

[Damian37]

no that should be good, your logic was right, you were just trying to check the format, which could have been 99/99/9999. You could have done this way using Regular Expressions, but anyway...

Dates are numbers of days since 1901 i believe, they are just formatted nicely for you, so ISDATE will check that for you.

When I attempt to submit the data via that add discrepancy button within my userform, I receive the following error. Run-time error '13': Type mismatch, and when I click on Debug, the ElseIf line is highlighted.

[Damian37]

What's the problem here, the code looks fine to me?

Please Login or Register  to view this content.

[/QUOTE]

Hi Norie,
The userform is still letting me place an amount within the txt.Amt.Value box after selecting Indirect. I'm trying to get it so when a user chooses Indirect and then also attempts to place an amount within the txt.Amt.Value text box they are given an error message. I can see I need to add a msgbox within the code to let them know they have entered an amount after choosing Indirect, but right now nothing is happening. Of course, I'm trying to get past the date validation issue nathansav is currently helping me with.

I commented out the date validation portion and attempted to run it to see if the direct/indirect validation was working, nothing is happening after I run it. I get no error, msgbox, or data submittion.
[code]
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim WS As Worksheet
Set WS = Worksheets("DetailOverShort")

'find first empty row in database
lRow = WS.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

ldate = Me.txtDate.Value

'check for a date
If Trim(Me.txtDate.Value) = "" Then
Me.txtDate.SetFocus
MsgBox "Please enter a Date"
'ElseIf IsDate(Me.txtDate.Value) <> "mm/dd/yyyy" Then
'Me.txtDate.SetFocus
' MsgBox "Please enter date in correct format"
'Else
'Me.txtDate.Value = ldate

End If

Exit Sub
'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected
With WS
.Unprotect Password:="password"
If MsgBox("Is all data correct?", vbYesNo) = vbNo Then

.Cells(lRow, 2).Value = Me.txtDate.Value
.Cells(lRow, 3).Value = Me.cboDirectIndirect.Value
.Cells(lRow, 4).Value = Me.txtPERNR.Value
.Cells(lRow, 5).Value = Me.txtName.Value
.Cells(lRow, 6).Value = Me.cboLocation.Value
.Cells(lRow, 10).Value = Me.txtAmt.Value
.Cells(lRow, 12).Value = Me.txtCorrection.Value
.Cells(lRow, 14).Value = Me.txtComments.Value
.Cells(lRow, 15).Value = Me.txtKioskNo.Value
.Cells(lRow, 16).Value = Me.txtKioskITDate.Value
.Cells(lRow, 17).Value = Me.txtKioskClosedDate.Value
.Cells(lRow, 18).Value = Me.txtKioskResolution.Value

' .Protect Password:="password"
End If
If cboDirectIndirect.Value = "Indirect" Then
txtAmt.Enabled = False
MsgBox ("You cannot enter a dollar amount when Discrepancy Type is set to Indirect!")
Else
txtAmt.Enabled = True
End If
Exit Sub
End With

'clear the data
Me.txtDate.Value = Format(Date, "Medium Date")
Me.cboDirectIndirect.Value = ""
Me.txtPERNR.Value = ""
Me.txtName.Value = ""
Me.cboLocation.Value = ""
Me.txtAmt.Value = ""
Me.txtCorrection.Value = ""
Me.txtComments.Value = ""
Me.txtKioskNo.Value = ""
Me.txtKioskITDate.Value = ""
Me.txtKioskClosedDate.Value = ""
Me.txtKioskResolution.Value = ""
End Sub
[/code

[Norie]

If txtAmt has been disable then the user can't enter a value in it, so you don't need a message if they enter something in it.

Mind you, are you sure the code is in the right place?

I would have thought it would have made more sense to have it in the change event of the combobox.

Then when they select Indirect the textbox will be disabled and they won't be able to enter anything in it.

Did you try the code I posted in post #2 for date validation?

[Damian37]

If txtAmt has been disable then the user can't enter a value in it, so you don't need a message if they enter something in it.

Mind you, are you sure the code is in the right place?

I would have thought it would have made more sense to have it in the change event of the combobox.

Then when they select Indirect the textbox will be disabled and they won't be able to enter anything in it.

Did you try the code I posted in post #2 for date validation?

Yes, the code you posted in post # 2 is the one I am using, but I am confused as to what you're referring to when you refer to the change event of the combobox. Is that the second line within the list of value parameters in the code? so like this?

Please Login or Register  to view this content.

[Norie]

No, it's an event of the combobox that's triggered when a selection is made.

As it is, with the code you have you, are checking the value of the combobox after the user has entered something in the textbox and after the data has been entered on the worksheet.

[Damian37]

No, it's an event of the combobox that's triggered when a selection is made.

As it is, with the code you have you, are checking the value of the combobox after the user has entered something in the textbox and after the data has been entered on the worksheet.

Ok, I think that's where my confusion lies, because I cannot see any sub coding for the combobox. The only reference within the coding for the combobox is the line you see within the coding I sent...

Please Login or Register  to view this content.

[Norie]

In design view double click the combobox.

[Damian37]

In design view double click the combobox.

OMG Norie!
I feel like such an idiot. I've actually done that double-clicking for a few texboxes I'm currently using within my userform. Wow. Well, that did it! Thanks so much. Now on to getting this date validation portion to work and I will then be on to the next validation, lol. I cannot thank you enough for pointing out the obvious!

[Damian37]

no that should be good, your logic was right, you were just trying to check the format, which could have been 99/99/9999. You could have done this way using Regular Expressions, but anyway...

Dates are numbers of days since 1901 i believe, they are just formatted nicely for you, so ISDATE will check that for you.

Hi nathansav,
I was called away on another project, but I've now been asked to come back to this one. I tried entering in the information you suggested within my code, but the userform is still allowing me to enter in the date however I want. I receive no message indicating my date is in the wrong format. I've attached an example of the spreadsheet with the command button that calls on the userform. Here is the code I'm using to try and validate my date:

Please Login or Register  to view this content.

I would also very much like to add a check that will not allow the user to enter in a date in the future or more than 7 days ago. Is that possible?

Thanks!
Damian37

[Norie]

Damian

You shouldn't worry about the format of the date, you should worry that the user has actually entered a valid date.

That can be done using IsDate, and if they have entered a valid date you can convert it to the desired format using Format.

[Damian37]

Damian

You shouldn't worry about the format of the date, you should worry that the user has actually entered a valid date.

That can be done using IsDate, and if they have entered a valid date you can convert it to the desired format using Format.

Thanks Norie,
So with the code that I showed in my last post, I have the format being set to mm/dd/yyyy. Does this line convert it to my desired format once they hit Add discrepancy?

Please Login or Register  to view this content.

Also, when I go ahead and enter in the data, I get a compile error message when attempting to add the discrepancy. The error is " Argument not optional". Not entirely sure what that's referring to.

[Norie]

IsDate is a function that takes a (string) value and returns True if VBA regards that value as a valid date.

To test if the value entered in a textbox is a valid date the code would look something like this.

Please Login or Register  to view this content.

[Damian37]

IsDate is a function that takes a (string) value and returns True if VBA regards that value as a valid date.

To test if the value entered in a textbox is a valid date the code would look something like this.

Please Login or Register  to view this content.

Thanks Norie,
I entered in some test discrepancies and even though I entered in the date 5/14/14, it showed up in my data sheet with the year showing all four digits. Although, it's curious, the format I have set within my code is mm/dd/yyyy, yet the format within the Date of Occurrence column shows 5/14/2014 and another column in which I concatenate three columns including the date appears 05142014. Would you happen to know why that is?

[Damian37]

Thanks Norie,
I entered in some test discrepancies and even though I entered in the date 5/14/14, it showed up in my data sheet with the year showing all four digits. Although, it's curious, the format I have set within my code is mm/dd/yyyy, yet the format within the Date of Occurrence column shows 5/14/2014 and another column in which I concatenate three columns including the date appears 05142014. Would you happen to know why that is?

Nevermind, I figured out why that is. It's the formatting within the cells. I'm good. Thanks!

[Damian37]

IsDate is a function that takes a (string) value and returns True if VBA regards that value as a valid date.

To test if the value entered in a textbox is a valid date the code would look something like this.

Please Login or Register  to view this content.

Thanks for the help Norie. Using IsDate, and formatting it the way I want it, now it doesn't matter how they enter in the date as long as they enter in a valid date. I did want to ask you however if you knew how I could go about not allowing the user to enter in any future dates, i.e. 07/1/2020 or being able to enter in a date older than 7 days i.e. 06/23/2014? Would I add another ElseIf statement? Can I even do that? Or do I need to add another If statement all together?

Please Login or Register  to view this content.

Would something like that work?

[Norie]

That's shouldn't be too much trouble.

For that you could use something along these lines.

Please Login or Register  to view this content.

That code would need to come after the code that checks the date is valid.

[Damian37]

That's shouldn't be too much trouble.

For that you could use something along these lines.

Please Login or Register  to view this content.

That code would need to come after the code that checks the date is valid.

Would it be possible to combine the two rather than have two if statements? Something like

Please Login or Register  to view this content.

[Damian37]

Would it be possible to combine the two rather than have two if statements? Something like

Please Login or Register  to view this content.

Norie, I tried to run it with the Or statement embedded within the If statement, and it didn't work. So I added it using two if statements and it works like a charm now. Thanks!

[Norie]

Glad it worked.

You could combine the 2 if statements but I thought it might be better to have them separate so you could be specific about the problem with the date the user has entered.

PS If you don't want the user to enter a date beyond today's date you could always give them a wee hint by setting the textbox value to the current date in the userform's Initialize event.

Please Login or Register  to view this content.

[Damian37]

Glad it worked.

You could combine the 2 if statements but I thought it might be better to have them separate so you could be specific about the problem with the date the user has entered.

PS If you don't want the user to enter a date beyond today's date you could always give them a wee hint by setting the textbox value to the current date in the userform's Initialize event.

Please Login or Register  to view this content.

I agree about the two if statements. Also, the initialize form to have the date already setup is genius! I added it to the code and it automatically entered in today's date. There's only one glitch, after 2014 it is giving me 182 and I'm thinking this might have something to do with VBA trying to add a timestamp on to the date. How would I go about taking the 182 out?

[Norie]

Oops, it should only be 4 y not 5 y.

[Damian37]

Oops, it should only be 4 y not 5 y.

Oh duh! I should've caught that. Still at work and trying to get out of here.Taking the extra y did it. Thanks for all your help. I'm trying to also add validation to some of the boxes within the userform to force the user to enter in values if another box has values. I'll start a new thread since it's a separate issue, but I would really appreciate your help on it.