invalid property value error with combobox

[RatherBeRidi]

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

[Tinbendr]

Excel is expecting a string from cboReportPeriod, so either

If cboReportPeriod <> "" then

or

If cboReportPeriod.ListIndex then 'No entry selected.

[RatherBeRidi]

Thanks for your reply. However, I still can't get it to work.

The msgbox always pops up and the user can't move to the next field using either of these:
If cboReportPeriod <> "" then
If cboReportPeriod.ListIndex <> "" then

Any other thoughts? If not, I could add a note on the userform that they need to complete the field if they see an invalid property value error message.

[Tinbendr]

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

[RatherBeRidi]

Thanks for your reply David. Using the last suggestion, the msgbox displays even if a valid entry is selected. If I make the change below, the msgbox displays the first time through the userform, but without closing the userform, the next time through the Invalid property value error displays. I've resorted to a label on the userform to let the user know what that means. Any other suggestions? Thanks.


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

[Jan Karel Pieterse]

Try exporting the userform, removing the userform and importing the exported form again (after opening the workbook with macro's disabled).
If that does not help, I suggest you do not tie the comboboxes to worksheet cells directly, but rather use the userforms initialise event to populate them from the worksheet with code like this:
ComboBox1.List=Worksheets("MyLists").Range("ComboBox1List").Value

[RatherBeRidi]

I get the values for the comboboxes from named ranges through the initialize event, and save the data from the userform to the Report worksheet with the cmdAdd, listed in first post, where I clear the fields at the end. The userforms remains open to continue entering records. The error message occurs when trying to enter any record after the first. I would like to disable the X for closing the userform, but only if I can get it to work without error messages. Thanks for your help!


Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To Range("ReportPeriodLookUp").Rows.Count
Me.cboReportPeriod.AddItem Range("ReportPeriodLookUp")(i)
Next i
For i = 1 To Range("PracticeName").Rows.Count
Me.cboPracticeName.AddItem Range("PracticeName")(i)
Next i
For i = 1 To Range("CM_Licensure").Rows.Count
Me.cboCMLicensure.AddItem Range("CM_Licensure")(i)
Next i
For i = 1 To Range("CMrole").Rows.Count
Me.cboCareManagerRole.AddItem Range("CMrole")(i)
Next i
For i = 1 To Range("PatientPopulation").Rows.Count
Me.cboPatientPopulation.AddItem Range("PatientPopulation")(i)
Next i
End Sub

[mikerickson]

I find things to be a lot more stable if I use the .Text property of a ComboBox (or TextBox) rather than the .Value property.

[Jan Karel Pieterse]

What happens if you don't set the Value properties to "", but rather set the listIndex to -1? I believe the error is caused by trying to set a value which is not in the list

[snb]

Do not use 'rowsource' but use 'List' instead; e.g.:


Private sub userform_Initialize()
cboReportPeriod.List=sheets(1).range("B1:B20").value
end sub


Do not use 'matchrequired=true', but 'matchrequired=false'