Hi all,
I've got a userform which populates the next available row in an excel spreadsheet.
There are 2 boxes on my userform which require dates to be entered. Therefore I attempted to put them in as a date value and put an error message on when a date format is not used for entry. However the error code does not appear when the date is entered incorrectly, I just get asked to debug the code. It works fine when the date is entered correctly.
The code is below for the OK button (ie the populate the spreadsheet button). The Date box 1 is called txtDateToday and the Date box 2 is called txtDateExpected.
Private Sub cmdOK_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Consolidation")
iRow = ws.Range("A:M").Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
RowCount = Worksheets("Consolidation").Range("A7").CurrentRegion.Rows.Count
With Worksheets("Consolidation").Range("A7")
ws.Cells(iRow, 1).Value = Me.cboCompany.Value
ws.Cells(iRow, 2).Value = DateValue(Me.txtDateToday.Value)
ws.Cells(iRow, 3).Value = Me.txtDivision.Value
ws.Cells(iRow, 4).Value = Me.txtTitle.Value
ws.Cells(iRow, 5).Value = Me.cboProjectType.Value
ws.Cells(iRow, 6).Value = Me.txtContact.Value
ws.Cells(iRow, 7).Value = DateValue(Me.txtDateExpected.Value)
ws.Cells(iRow, 9).Value = Me.cboPerson.Value
ws.Cells(iRow, 10).Value = Me.txtScope.Value
ws.Cells(iRow, 11).Value = Me.cboAllocation.Value
End With
If Me.cboCompany.Value = "" Then
MsgBox "Please enter the company the project is with", vbExclamation, "Project Entry Form Error"
Me.cboCompany.SetFocus
Exit Sub
End If
If Not IsDate(Me.txtDateToday.Value) Then
MsgBox "The Date Today box must contain a date.", vbExclamation, "Project Entry Form Error"
Me.txtDateToday.SetFocus
Exit Sub
End If
If Me.txtDivision.Value = "" Then
MsgBox "Please enter the division of the company.", vbExclamation, "Project Entry Form Error"
Me.txtDivision.SetFocus
Exit Sub
End If
If Me.txtTitle.Value = "" Then
MsgBox "Please enter in some information about the project.", vbExclamation, "Project Entry Form Error"
Me.txtTitle.SetFocus
Exit Sub
End If
If Me.cboProjectType.Value = "" Then
MsgBox "Please enter in the type of project.", vbExclamation, "Project Entry Form Error"
Me.cboProjectType.SetFocus
Exit Sub
End If
If Me.txtContact = "" Then
MsgBox "Please identify whether the contact that this project was sourced from.", vbExclamation, "Project Entry Form Error"
Me.txtContact.SetFocus
Exit Sub
End If
If Not IsDate(Me.txtDateExpected.Value) Then
MsgBox "The Date Expected box must contain a date.", vbExclamation, "Project Entry Form Error"
Me.txtDateExpected.SetFocus
Exit Sub
End If
If Me.cboPerson.Value = "" Then
MsgBox "Please enter in the initials of the person who sourced this project.", vbExclamation, "Project Entry Form Error"
Me.cboPerson.SetFocus
Exit Sub
End If
If Me.txtScope.Value = "" Then
MsgBox "Please enter in the estimated scope of the project.", vbExclamation, "Project Entry Form Error"
Me.txtScope.SetFocus
Exit Sub
End If
If Me.cboAllocation.Value = "" Then
MsgBox "Please enter in type of allocation of the project.", vbExclamation, "Project Entry Form Error"
Me.cboAllocation.SetFocus
Exit Sub
End If
Unload Me
End Sub
Bookmarks