Private Sub btnImport_Click()
Dim lRow As Long, ws As Worksheet, f As Range
If MsgBox("Existing userform data will be overwritten. Are you sure that you want to add data from the row selected?", _
vbYesNo, "Add Spreadsheet Data from Row Selected") = vbNo Then Exit Sub
ClearForm
Set ws = Worksheets("Sheet1")
With ws
lRow = Selection.Row
txtdate.Value = .Cells(lRow, "A").Value
txtJobNumber.Value = .Cells(lRow, "B").Value
txtRaisedBy.Value = .Cells(lRow, "C").Value
txtProjectManager.Value = .Cells(lRow, "D").Value
cboSeverity.Value = .Cells(lRow, "E").Value
txtCause.Value = .Cells(lRow, "F").Value
txtCorrectiveActions.Value = .Cells(lRow, "G").Value
txtPreventativeActions.Value = .Cells(lRow, "H").Value
txtTimeTakenMins.Value = .Cells(lRow, "I").Value
txtMaterialCost.Value = .Cells(lRow, "J").Value
txtTimeToReInspectMins.Value = .Cells(lRow, "K").Value
cboProblemCategory.Value = .Cells(lRow, "L").Value
txtTotalTime.Value = .Cells(lRow, "M").Value
obClosed.Value = .Cells(lRow, "N").Value
txtNCRnumber.Value = .Cells(lRow, "O").Value
End With
End Sub
Private Sub btnNewNCRno_Click()
If MsgBox("Existing userform data will be overwritten. Are you sure that you want to add new data?", _
vbYesNo, "Add New NCR Number") = vbYes Then
ClearForm
txtNCRnumber.Value = NextNCRNumber
End If
End Sub
Private Sub ClearForm()
'Clear input controls.
Me.txtdate.Value = ""
Me.txtJobNumber.Value = ""
Me.txtRaisedBy.Value = ""
Me.txtProjectManager.Value = ""
Me.cboSeverity.Value = ""
Me.txtCause.Value = ""
Me.txtCorrectiveActions.Value = ""
Me.txtPreventativeActions.Value = ""
Me.txtTimeTakenMins.Value = ""
Me.txtMaterialCost.Value = ""
Me.txtTimeToReInspectMins.Value = ""
Me.cboProblemCategory.Value = ""
Me.txtTotalTime.Value = ""
Me.obClosed.Value = ""
txtNCRnumber.Value = ""
End Sub
'Copy input values to sheet.
Private Sub cmdSaveNCR_Click()
Dim lRow As Long, ws As Worksheet, f As Range
Set ws = Worksheets("Sheet1")
With ws
lRow = .Cells(Rows.Count, "O").End(xlUp).Offset(1, 0).Row
Set f = .Range("O2", .Range("O" & Rows.Count).End(xlUp))
Set f = f.Find(What:=txtNCRnumber, after:=f.Cells(f.Rows.Count, f.Columns.Count), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not f Is Nothing Then
If MsgBox(txtNCRnumber & " exists, overwrite data?", vbYesNo, "Overwrite?") = vbNo Then
Exit Sub
Else
lRow = f.Row
End If
End If
.Cells(lRow, "A").Value = txtdate.Value
.Cells(lRow, "B").Value = txtJobNumber.Value
.Cells(lRow, "C").Value = txtRaisedBy.Value
.Cells(lRow, "D").Value = txtProjectManager.Value
.Cells(lRow, "E").Value = cboSeverity.Value
.Cells(lRow, "F").Value = txtCause.Value
.Cells(lRow, "G").Value = txtCorrectiveActions.Value
.Cells(lRow, "H").Value = txtPreventativeActions.Value
.Cells(lRow, "I").Value = txtTimeTakenMins.Value
.Cells(lRow, "J").Value = txtMaterialCost.Value
.Cells(lRow, "K").Value = txtTimeToReInspectMins.Value
.Cells(lRow, "L").Value = cboProblemCategory.Value
.Cells(lRow, "M").Value = txtTotalTime.Value
.Cells(lRow, "N").Value = obClosed.Value
.Cells(lRow, "O").Value = txtNCRnumber.Value
End With
Unload Me
UserForm1.Show vbModeless
End Sub