Solved: ComboBox error: Invalid property value

[JimmyTheHand]

Hi

I've gtot a form with 30 comboboxes. During form initialization they recieve an array to their list property. I want to limit the entered values to list items, so I set the MatchRequired property to True.

I also want to have the option to leave the combobox empty, so I always set the first element of the list to an empty string:
[vba] ComboBox1.List(0)=""[/vba]
Now, when I "accidentally" type something into a combobox, then delete it with Del key, I cannot leave the combobox: it gives me the error message indicated in the thread title. Even though the list does contain an empty string, and the current value of the combobox is also an empty string, I cannot leave the box. I need to select the empty string from the list, an only then can I leave.

See what I mean in the attached workbook.

I want to use the Del key, because data entry is lot faster and more convenient via keyboard than mouseclicking. How could I convince my comboboxes that a deleted string is really an empty string?

Thanks in advance

Jimmy

[XLGibbs]

If entry is required into that combo box, and they must select something from the list, why have the empty string as a choice?

You can look at capturing the delete key presses ( IF KeyCode = 46 then...) or you can use on error for combo box selection/change event handling

[JimmyTheHand]

XLGibbs,

Entry is not required. I tried to say that the user should either leave the combobox empty, or, if data is entered, it must be selected from the list. Sorry for being unclear.

I'm not sure how you meant to solve the problem with error handlers. The ComboBox_Change event never caused exception. I also tried this:
[VBA]Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo Err_h
Exit Sub
Err_h:
ComboBox1.ListIndex = 0
End Sub[/VBA]

Unfortunately, the "Invalid property Value" message comes before the event code is activated, so it does not work.

Your idea about capturing key presses is working, however, it means that I need to write 30 event handler for the 30 comboboxes... which I will if I must, but in the meantime I'm still looking for a simpler solution.

Thank you for the tips, anyway

Jimmy

[Bob Phillips]

Userform

[vba]

Dim mcolEvents As Collection

Private Sub UserForm_Initialize()
Dim cComboEvents As clsUserFormEvents
Dim ctl As MSForms.Control

Set mcolEvents = New Collection

For Each ctl In Me.Controls
If TypeName(ctl) = "ComboBox" Then
Set cComboEvents = New clsUserFormEvents
Set cComboEvents.mComboGroup = ctl
mcolEvents.Add cComboEvents
End If
Next

End Sub
[/vba]

Class module

[vba]

Option Explicit

Public WithEvents mComboGroup As MSForms.ComboBox


Private Sub mcomboGroup_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
MsgBox mComboGroup.Name
End Sub
[/vba]

[JimmyTheHand]

Thanks (again ), Bob.

At first the code you gave didn't work, then I figured out that DEL key doesn't raise the KeyPress event, so KeyUp is needed instead. Then, again, I figured out that I do not need, at all, to check what key was pressed. The only important thing is that when the box becomes empty, it's value must change to the empty string from the list.

So for benefit of future generations, I gladly attach the solution.

Jimmy