userform combobox requirement

[RatherBeRidi]

I need to require entry of the first field of a userform, cboReportPeriod. I tried using Private Sub cboReportPeriod_Exit (below), but the focus moves to the next field instead of remaining in cboReportPeriod. And I'm concerned that even if I get this working correctly, will the user be able to select another field with the mouse and not enter the required information? Would it be better to validate if a choice was made in the Private Sub cmdAdd? If so, I don't know if I would place the IF statement within the "With" or elsewhere. Thanks for advice on which method to use and how to do it.

This code does not return focus to cboReportPeriod:
Private Sub cboReportPeriod_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim cbo As ComboBox
If cboReportPeriod.Value = "" Then
MsgBox "Please choose report period.", vbCritical, "Entry Required"
End If
Me.cboReportPeriod.SetFocus
End Sub


Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim txt As TextBox
Dim cbo As ComboBox
Dim ws As Worksheet
Set ws = Worksheets("Report")
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

lPart = Me.cboPracticeName.ListIndex
ws.Unprotect ("XXX")
'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.txtCHOID.Value
.Cells(lRow, 2).Value = Me.cboReportPeriod.Value
.Cells(lRow, 3).Value = Me.cboPracticeName.Value
'column4 auto populated by formula in worksheet
.Cells(lRow, 5).Value = Me.txtCareManagerName.Value
.Cells(lRow, 6).Value = Me.cboCareManagerRole.Value
.Cells(lRow, 7).Value = Me.txtFTE.Value
.Cells(lRow, 8).Value = Me.cboPatientPopulation.Value
.Cells(lRow, 9).Value = Me.txtDateBegan.Value
.Cells(lRow, 10).Value = Me.txtDateTraining.Value
.Cells(lRow, 11).Value = Me.txtCareManagerPhone.Value
.Cells(lRow, 12).Value = Me.txtCareManagerEmail.Value
.Cells(lRow, 13).Value = Me.txtReportsTo.Value
.Cells(lRow, 14).Value = Me.txtSupervisorEmail.Value
.Cells(lRow, 15).Value = Me.txtSupervisorPhone.Value
End With
ws.Protect ("XXX"), UserInterfaceOnly:=True
'clear the data
Me.txtCHOID.Value = ""
Me.cboReportPeriod.Value = ""
Me.cboPracticeName.Value = ""
Me.txtCareManagerName.Value = ""
Me.cboCareManagerRole.Value = ""
Me.txtFTE.Value = ""
Me.cboPatientPopulation.Value = ""
Me.txtDateBegan.Value = ""
Me.txtDateTraining.Value = ""
Me.txtCareManagerPhone.Value = ""
Me.txtCareManagerEmail.Value = ""
Me.txtReportsTo.Value = ""
Me.txtSupervisorEmail.Value = ""
Me.txtSupervisorPhone.Value = ""
Me.txtCHOID.SetFocus
End Sub

[Bob Phillips]

Would it be better to validate if a choice was made in the Private Sub cmdAdd?

Yes.

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim txt As TextBox
Dim cbo As ComboBox
Dim ws As Worksheet

If Me.cboReportPeriod.Value = "" Then

MsgBox "Report Period must be entered"
Me.cboReportPeriod.SetFocus
Else

Set ws = Worksheets("Report")
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

lPart = Me.cboPracticeName.ListIndex
ws.Unprotect ("XXX")
'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.txtCHOID.Value
.Cells(lRow, 2).Value = Me.cboReportPeriod.Value
.Cells(lRow, 3).Value = Me.cboPracticeName.Value
'column4 auto populated by formula in worksheet
.Cells(lRow, 5).Value = Me.txtCareManagerName.Value
.Cells(lRow, 6).Value = Me.cboCareManagerRole.Value
.Cells(lRow, 7).Value = Me.txtFTE.Value
.Cells(lRow, 8).Value = Me.cboPatientPopulation.Value
.Cells(lRow, 9).Value = Me.txtDateBegan.Value
.Cells(lRow, 10).Value = Me.txtDateTraining.Value
.Cells(lRow, 11).Value = Me.txtCareManagerPhone.Value
.Cells(lRow, 12).Value = Me.txtCareManagerEmail.Value
.Cells(lRow, 13).Value = Me.txtReportsTo.Value
.Cells(lRow, 14).Value = Me.txtSupervisorEmail.Value
.Cells(lRow, 15).Value = Me.txtSupervisorPhone.Value
End With
ws.Protect ("XXX"), UserInterfaceOnly:=True
'clear the data
Me.txtCHOID.Value = ""
Me.cboReportPeriod.Value = ""
Me.cboPracticeName.Value = ""
Me.txtCareManagerName.Value = ""
Me.cboCareManagerRole.Value = ""
Me.txtFTE.Value = ""
Me.cboPatientPopulation.Value = ""
Me.txtDateBegan.Value = ""
Me.txtDateTraining.Value = ""
Me.txtCareManagerPhone.Value = ""
Me.txtCareManagerEmail.Value = ""
Me.txtReportsTo.Value = ""
Me.txtSupervisorEmail.Value = ""
Me.txtSupervisorPhone.Value = ""
Me.txtCHOID.SetFocus
End If
End Sub

[RatherBeRidi]

Works perfect! Thank you.