how to generate date error message

[pilgrimm]

I have a user form that works great but I have one last item to fix.
If someone types in a date wrong, it will not update the spreadsheet
with any info and does not give an option to fix the date. What I
would like is an error message and not to update the spreadsheet but
to be able to fix the date. Once that is done, click on OK and then
it can update sheet.
Here is what I have:


Private Sub OK_Click()
Dim RowCount As Long

If Me.txtName.Value = "" Then
MsgBox "Please enter Employee's name", vbExclamation,
"PSHCPNUMBERS"
Me.txtName.SetFocus
Exit Sub
End If
If Me.TXTPRI.Value = "" Then
MsgBox "Please enter Employee's PRI", vbExclamation,
"PSHCPNUMBERS"
Me.txtName.SetFocus
Exit Sub
End If
If Me.CBODEPARTMENT.Value = "" Then
MsgBox "Please Choose a Department", vbExclamation,
"PSHCPNUMBERS"
Me.txtName.SetFocus
Exit Sub
End If
If Me.cboPSHCPLEVEL.Value = "" Then
MsgBox "Please Choose PSHCP Level", vbExclamation,
"PSHCPNUMBERS"
Me.txtName.SetFocus
Exit Sub
End If
If Me.TXTDEDUCTIONDATE.Value = "" Then
MsgBox "Please Enter a Deducton Date", vbExclamation,
"PSHCPNUMBERS"
Me.txtName.SetFocus
Exit Sub
End If
If Me.TXTCOVERAGEDATE.Value = "" Then
MsgBox "Please Enter a Coverage Date", vbExclamation,
"PSHCPNUMBERS"
Me.txtName.SetFocus
Exit Sub
End If
RowCount =
Worksheets("PSHCP").Range("A1").CurrentRegion.Rows.Count
With Worksheets("PSHCP").Range("A1")
.Offset(RowCount, 7).Value = Format(Now, "dd/mmm/yyyy
hh:nn:ss") & Application.UserName
.Offset(RowCount, 0).Value = Me.txtName.Value
.Offset(RowCount, 1).Value = Me.TXTPRI.Value
.Offset(RowCount, 2).Value = Me.CBODEPARTMENT.Value
.Offset(RowCount, 3).Value = Me.cboPSHCPLEVEL.Value
.Offset(RowCount, 4).Value = DateValue(TXTDEDUCTIONDATE.Value)
.Offset(RowCount, 5).Value = DateValue(TXTCOVERAGEDATE.Value)
End With
Unload Me


End Sub

 

[David]

What represents a "wrong" date? Todays date? A specific date? A date tht does
not conform to date format?

 

[pilgrimm]

What represents a "wrong" date? Todays date? A specific date? A date tht does
not conform to date format?








- Show quoted text -

A date that does not conform to the date format.
For example, if someon entered 18-18-2008 instead of 18-02-2008 the
date info updated to the spreadsheet would be blank. I am trying to
find a way so that if the date does not match the standard format (dd-
mm-yy or some version of this)to indicate an error and re-type the
date.

 

[Ker_01]

Have you tried the VBA function IsDate? Pasted from VBE help:
Returns a Boolean value indicating whether an expression can be converted to
a date.
Syntax

IsDate(expression)

The required expression argument is a Variant containing a date expression
or string expression recognizable as a date or time.

Remarks

IsDate returns True if the expression is a date or is recognizable as a
valid date; otherwise, it returns False. In Microsoft Windows, the range of
valid dates is January 1, 100 A.D. through December 31, 9999 A.D.; the
ranges vary among operating systems

What represents a "wrong" date? Todays date? A specific date? A date tht
does
not conform to date format?








- Show quoted text -

A date that does not conform to the date format.
For example, if someon entered 18-18-2008 instead of 18-02-2008 the
date info updated to the spreadsheet would be blank. I am trying to
find a way so that if the date does not match the standard format (dd-
mm-yy or some version of this)to indicate an error and re-type the
date.

 

[pilgrimm]

Have you tried the VBA function IsDate? Pasted from VBE help:
Returns a Boolean value indicating whether an expression can be converted to
a date.
Syntax

IsDate(expression)

The required expression argument is a Variant containing a date expression
or string expression recognizable as a date or time.

Remarks

IsDate returns True if the expression is a date or is recognizable as a
valid date; otherwise, it returns False. In Microsoft Windows, the range of
valid dates is January 1, 100 A.D. through December 31, 9999 A.D.; the
ranges vary among operating systems






A date that does not conform to the date format.
For example, if someon entered 18-18-2008 instead of 18-02-2008 the
date info updated to the spreadsheet would be blank.   I am trying to
find a way so that if the date does not match the standard format (dd-
mm-yy or some version of this)to indicate an error and re-type the
date.- Hide quoted text -

- Show quoted text -

I am not all that familiar with the IsDate function.

What I am trying to do is reduce the chance of an run time error.

If I could somehow have a message come up that the 'date is not
formatted correct, please correct'
before updating to the spreadsheet, that would be what I want.

Using the IsDate, I still get that run time error.

 

[Ker_01]

Please post the runtime error you are getting, and which line of code it
stops on. Also a sample of what you have tried (and isn't working) with
isdate. e.g.,

If Not(IsDate(Me.TXTDEDUCTIONDATE.Value)) Then...
msgbox
exit sub

Keith

Have you tried the VBA function IsDate? Pasted from VBE help:
Returns a Boolean value indicating whether an expression can be converted
to
a date.
Syntax

IsDate(expression)

The required expression argument is a Variant containing a date expression
or string expression recognizable as a date or time.

Remarks

IsDate returns True if the expression is a date or is recognizable as a
valid date; otherwise, it returns False. In Microsoft Windows, the range
of
valid dates is January 1, 100 A.D. through December 31, 9999 A.D.; the
ranges vary among operating systems






A date that does not conform to the date format.
For example, if someon entered 18-18-2008 instead of 18-02-2008 the
date info updated to the spreadsheet would be blank. I am trying to
find a way so that if the date does not match the standard format (dd-
mm-yy or some version of this)to indicate an error and re-type the
date.- Hide quoted text -

- Show quoted text -

I am not all that familiar with the IsDate function.

What I am trying to do is reduce the chance of an run time error.

If I could somehow have a message come up that the 'date is not
formatted correct, please correct'
before updating to the spreadsheet, that would be what I want.

Using the IsDate, I still get that run time error.