RatherBeRidi
06-15-2012, 08:05 AM
With a userform, a worksheet (Report) is populated using cmdAdd button. Values of the textboxes and comboboxes are cleared in the code for this button, and the form remains open to enter more records. If data was entered in a combobox for the first record, the combobox cannot be left blank on subsequent entries. An "Invalid Property Value" error displays. I read that is caused by the value of the combobox is -1 and found code to display a message if that happens, but I receive the same message. I hope someone can identify where my error is. Thanks.
The first try:
Private Sub cboReportPeriod_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If cboReportPeriod < 0 Then
MsgBox "Please Select Report Period"
Cancel = True
End If
End Sub
Because the values are reset in the cmdAdd code, I tried the following, but the same error message displays:
Private Sub cboReportPeriod_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If cboReportPeriod <= 0 Then
MsgBox "Please Select Report Period"
Cancel = True
End If
End Sub
In case it will help, the code for cmdAdd is below:
Private Sub cmdAdd_Click()
Dim lRow 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
ws.Unprotect ("XXX")
'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.cboReportPeriod.Value
.Cells(lRow, 2).Value = Me.cboPracticeName.Value
'column3 auto populated by formula in worksheet
.Cells(lRow, 4).Value = Me.txtCMFirstName.Value
.Cells(lRow, 5).Value = Me.txtCMLastName.Value
.Cells(lRow, 6).Value = Me.cboCMLicensure.Value
.Cells(lRow, 7).Value = Me.cboCareManagerRole.Value
.Cells(lRow, 8).Value = Me.txtFTE.Value
.Cells(lRow, 9).Value = Me.cboPatientPopulation.Value
.Cells(lRow, 10).Value = Me.txtDateBegan.Value
.Cells(lRow, 11).Value = Me.cboRegistration.Value
.Cells(lRow, 12).Value = Me.txtCareManagerPhone.Value
.Cells(lRow, 13).Value = Me.txtCareManagerEmail.Value
.Cells(lRow, 14).Value = Me.txtReportsTo.Value
.Cells(lRow, 15).Value = Me.txtSupervisorEmail.Value
.Cells(lRow, 16).Value = Me.txtSupervisorPhone.Value
End With
ws.Protect ("XXX"), UserInterfaceOnly:=True
'clear the data
'Me.txtCHOID.Value = ""
Me.cboReportPeriod.Value = ""
Me.cboPracticeName.Value = ""
Me.txtCMFirstName.Value = ""
Me.txtCMLastName.Value = ""
Me.cboCMLicensure.Value = ""
Me.cboCareManagerRole.Value = ""
Me.txtFTE.Value = ""
Me.cboPatientPopulation.Value = ""
Me.txtDateBegan.Value = ""
Me.cboRegistration.Value = ""
Me.txtCareManagerPhone.Value = ""
Me.txtCareManagerEmail.Value = ""
Me.txtReportsTo.Value = ""
Me.txtSupervisorEmail.Value = ""
Me.txtSupervisorPhone.Value = ""
Me.txtCHOID.SetFocus
End If
End Sub
The first try:
Private Sub cboReportPeriod_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If cboReportPeriod < 0 Then
MsgBox "Please Select Report Period"
Cancel = True
End If
End Sub
Because the values are reset in the cmdAdd code, I tried the following, but the same error message displays:
Private Sub cboReportPeriod_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If cboReportPeriod <= 0 Then
MsgBox "Please Select Report Period"
Cancel = True
End If
End Sub
In case it will help, the code for cmdAdd is below:
Private Sub cmdAdd_Click()
Dim lRow 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
ws.Unprotect ("XXX")
'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.cboReportPeriod.Value
.Cells(lRow, 2).Value = Me.cboPracticeName.Value
'column3 auto populated by formula in worksheet
.Cells(lRow, 4).Value = Me.txtCMFirstName.Value
.Cells(lRow, 5).Value = Me.txtCMLastName.Value
.Cells(lRow, 6).Value = Me.cboCMLicensure.Value
.Cells(lRow, 7).Value = Me.cboCareManagerRole.Value
.Cells(lRow, 8).Value = Me.txtFTE.Value
.Cells(lRow, 9).Value = Me.cboPatientPopulation.Value
.Cells(lRow, 10).Value = Me.txtDateBegan.Value
.Cells(lRow, 11).Value = Me.cboRegistration.Value
.Cells(lRow, 12).Value = Me.txtCareManagerPhone.Value
.Cells(lRow, 13).Value = Me.txtCareManagerEmail.Value
.Cells(lRow, 14).Value = Me.txtReportsTo.Value
.Cells(lRow, 15).Value = Me.txtSupervisorEmail.Value
.Cells(lRow, 16).Value = Me.txtSupervisorPhone.Value
End With
ws.Protect ("XXX"), UserInterfaceOnly:=True
'clear the data
'Me.txtCHOID.Value = ""
Me.cboReportPeriod.Value = ""
Me.cboPracticeName.Value = ""
Me.txtCMFirstName.Value = ""
Me.txtCMLastName.Value = ""
Me.cboCMLicensure.Value = ""
Me.cboCareManagerRole.Value = ""
Me.txtFTE.Value = ""
Me.cboPatientPopulation.Value = ""
Me.txtDateBegan.Value = ""
Me.cboRegistration.Value = ""
Me.txtCareManagerPhone.Value = ""
Me.txtCareManagerEmail.Value = ""
Me.txtReportsTo.Value = ""
Me.txtSupervisorEmail.Value = ""
Me.txtSupervisorPhone.Value = ""
Me.txtCHOID.SetFocus
End If
End Sub