| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to generate date error message
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how to generate date error message
What represents a "wrong" date? Todays date? A specific date? A date tht does
not conform to date format? " wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how to generate date error message
On Feb 15, 4:40*pm, David wrote:
What represents a "wrong" date? Todays date? A specific date? A date tht does not conform to date format? " wrote: 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- Hide quoted text - - 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
how to generate date error message
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 wrote in message ... On Feb 15, 4:40 pm, David wrote: What represents a "wrong" date? Todays date? A specific date? A date tht does not conform to date format? " wrote: 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- Hide quoted text - - 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Error message needed if incorrect Date Of Birth (DOB) entered | Excel Worksheet Functions | |||
| Date error message (VBA) | Excel Programming | |||
| Find Date error message | Excel Programming | |||
| Adding a date to calender to automatially generate another date? | Excel Worksheet Functions | |||
| Input Date Error Message | Excel Programming | |||
Linear Mode
