User Form ComboBox - Invalid Property Type

[mikeymay]

I have a number of comboboxes in a form and I am (again) experiencing an issue with the Invalid Property Type error when interacting with the comboboxes and I just can't seem to nail a solution.

Code when the control is entered

Private Sub cbA_Type_Enter()

cbA_Type.MatchRequired = True

End Sub

This allow the user to accidently type in the cell rather then clicking on the drop down
Code for the Change event

Private Sub cbA_Type_Change()

'Just some checks so clearing the form, etc doesn't execute uneccessary code
If boolPF = True Or boolExitSub = True Or strPage <> "ADDRESS" Then
   Exit Sub
   Else
End If

'If the control is cleared, turn off match reqd otherwise
If cbA_Type.Value = "" Then
   cbA_Type.MatchRequired = False
   Else
   cbA_Type.MatchRequired = True
End If

'If the character typed isn't in the RowSource, clear it
If cbA_Type <> "" And WorksheetFunction.CountIf(wbPD.Sheets("Controls").Range("D_ControlsPT"), cbA_Type) = 0 Then
   boolExitSub = True

   cbA_Type.Value = ""

   boolExitSub = False

   Exit Sub
   Else
End If

CheckSave

If cbA_Type = "" Then
   cbA_Type.SetFocus
   Else
   txtA_A1.SetFocus
End If

End Sub

The error is showing when I perform the following

Select of an item is made​

The control is exited​

The selected control has an entry typed in it (it is a textbox)​

The combobox is selected again​

The selection is cleared​

Typing of a character that isn;t the first character of an value sin the row source - This doesn't register as the Change event clears it​

TAB is pressed to move to the next control​

The INvalid property Type Error shows​

Incredibly frustration and no amount of tinkering with the code seems to fix it.


TIA

 

[Anthony47]

This allow the user to accidently type in the cell rather then clicking on the drop down

If you think the user should only select from the list, you might use a ListBox instead of a Combobox

What for do you play with cbA_Type.MatchRequired = False /True?

If cbA_Type <> "" And WorksheetFunction.CountIf(wbPD.Sheets("Controls").Range("D_ControlsPT"), cbA_Type) = 0 Then

I seem that this code always clear the combobox; maybe you should use

If WorksheetFunction.CountIf(wbPD.Sheets("Controls").Range("D_ControlsPT"), cbA_Type & "*") = 0 Then

But I seem that using the listbox should be in line with your desired interface

 

[mikeymay]

If you think the user should only select from the list, you might use a ListBox instead of a Combobox

What for do you play with cbA_Type.MatchRequired = False /True?


I seem that this code always clear the combobox; maybe you should use

If WorksheetFunction.CountIf(wbPD.Sheets("Controls").Range("D_ControlsPT"), cbA_Type & "*") = 0 Then

But I seem that using the listbox should be in line with your desired interface

It needs to be a comboxbox rather than a list box.

I did mange to find a solution I....

When clearing a combobox entry (as part of other controls being intereactd with), using combobox = "" isn;t liked and I need to use combobox.listindex = -1 which seems to be working

 

[mikerickson]

If you want the user to not type into the combbox, set the default Style of the combbox to fmStyleDropDownList.

Also, changing properties like .MatchRequired in the middle of Change and Enter events is (IMO) poor practice.

 

[mikeymay]

If you want the user to not type into the combbox, set the default Style of the combbox to fmStyleDropDownList.

Also, changing properties like .MatchRequired in the middle of Change and Enter events is (IMO) poor practice.

Thanks Mike

A simple solution to something I've been having issues with forever....

 

[mikeymay]

If you want the user to not type into the combbox, set the default Style of the combbox to fmStyleDropDownList.

Also, changing properties like .MatchRequired in the middle of Change and Enter events is (IMO) poor practice.

Unfortunately I spoke too soon....

I am having the problem again when clearing a different combobox.

When the offending combobox is being cleared I am using the following code

With cbR_Room
   .Locked = False
     
   .ListIndex = -1
End With

The properties for the control are
MatchRequired = True
Style = 2 - frmStyleDropDownList

When the above codes runs, I get the error message and also when I close the form but when I step through it I don't get the error until I close the form.

 

[mikeymay]

It seems by setting the MatchRequired to False in the form design stops this error.

As the style is a list rather than a combo then the user can't type anythign in the control therefore, MatchRquired = False doesn;t cause any problems.