PDA

View Full Version : userform combobox error



RatherBeRidi
06-11-2012, 07:46 AM
I have a userform that populates a protected worksheet with the cmdAdd button. The form remains open to allow entry of additional records until the user selects the cmdClose button. The form is a mix of textboxes and comboboxes. The values for the comboboxes come from named ranges on a separate worksheet. Only one field, cboReportPeriod, is required. The form is working as I want - data is added to the appropriate worksheet/cells, cell values are cleared and I can continuously add records while keeping the form open. Except, one error message that I don't think will be experienced by the user, but would like to know if there is a solution. When the form is first opened, any field can be left blank. (The cmdAdd code checks the cboReportPeriod.) Keeping the form open to enter subsequent records, if any of the comboboxes that had an entry previously are skipped, an "Invalid property value" error for Microsoft Forms displays. Selecting OK, the focus remains in the combobox and an entry can be selected, but cannnot move out of the combobox. I read that checking the value of the combox box would solve this, but I still received the message (code at the end). Do I need to initialize the form between each entry? Or close and open the form between each entry?

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
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

Private Sub cboReportPeriod_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If cboReportPeriod < 0 Then
MsgBox "Please Select Report Period"
Cancel = True
End If
End Sub