User Form Invalid Property Error

[SteveG]

Hi All,

I've searched around for a while now and can't find the answer so I'm hoping someone can help.

I have a workbook with several Combo Boxes being used in a User Form. The RowSource is set to a named range in the workbook. This range includes a blank cell. My objective is to restrict users from populating the ComboBox with anything other than what is in the RowSource. MatchEntry is set to 1 - frmMatchEntryComplete and MatchRequired is set to True.

The User Form is launched using a command button on a sheet in the workbook.


Private Sub CommandButton6_Click()
frmAddRequest.Show
End Sub


When I launch the User Form I get the Microsoft Forms - Invalid Property error message. I am using the code below to initialize the form.


Private Sub UserForm_Initialize()
cboLSRName.Value = ""
cboTeam.Value = ""
cboUserList.Value = ""
DTPicker1.Value = Date
optSick = False
optVacation = False
optFloat = False
optFlex = False
cboLSRName.SetFocus
End Sub

I assume that the error is being caused by setting the Combo Box Values to "" but I'm not sure how to get around that. Any help is much appreciated!

Thanks,
Steve

[Bob Phillips]

Can you post the workbook to save us re-creating it all?

[mikerickson]

to select nothing from the list try

.cboLSRName.ListIndex = -1
The UserForm_Initialize routine is triggering Change events in all of those controls. That might be a source of error.

[Dr.K]

This is kind of a random stab, but I've had issues with UF ComboBoxes myself, so...

Where did you get this from?

cboTeam.Value = ""

I generally avoid using .Value for a ComboBox, unless I am specifically addressing the Bound Column. This is how I set ComboBoxes to empty:


cboTeam.ListIndex = -1

That seems much more reliable, but as you've noted, it does depend a bit on the behaviors you've selected for the control.


EDIT: DOH, beaten like a dead horse!

[SteveG]

Here is the workbook. It's a work in progress and since I'm still learning it's probably a bit of a mess to the trained eye so please bear with my lack of proficiency!

Just one note that it was working fine without the MatchRequired set to False.


Thanks in advance!
Steve

[SteveG]

Dr. K & mikerickson,

Thanks for your feedback. I tried your suggestions but still get the same results.

Thanks again.
Steve

[SteveG]

Well I was able to stop the Invalid Property error from happening by doing the following.

I changed the Combo Box .Value to .Text in the UserForm_Initialize and then for each Combo Box I added:


Private Sub cboTeam_Change()
If cboTeam.MatchFound = False Then
cboTeam.Text = ""
End If
End Sub

This seems to be working and does not allow users to enter anything unless a match is found. Not sure if it's the best way but it produced the desired results. Although this works for me I welcome any other feedback on the above method!

Thanks for your suggestions.

Steve

[Dr.K]

Are you using the ComboBox as a true ComboBox, or as a simple drop down box?

If you are doing the latter, you don't need to use "MatchRequired" at all.

[SteveG]

Dr. K,

I am really just using it as a drop down box.

Thanks,
Steve