Smart ComboBox control (for Excel) 1

[Kimed]

Hi,

In my Excel application, I need a control that would allow both to select a value from an existing list and to enter a value from a keybord that is *not* in the list. Initially, I had the ComboBox just for selection from the list and new values were entered into an Excel's cell, but it was too confusing to users who are forced to address a single article in two different places, so I'm searching for a way to merge both functions. To monitor selection from the list, I use ComboBox_Change event, but it also reacts to every keypress as if the single letter or a first existing value that starts with that letter was a right value to process. I'd like to separate ComboBox' reaction to keyboard from full-fledged values' selection from the list.

As a trial, I used the following code:

Dim KeyPr as Integer

Private Sub MyCombo_KeyPress(ByVal KeyANSI As MSForms.ReturnInteger)
KeyPr = 1
End Sub

Private Sub MyCombo_Change()
    If KeyPr = 1 Then
        KeyPr = 0
        Exit Sub
    End If
    KeyPr = 0
    (operational code)
End Sub

It *almost* worked... until I found that pressing Delete or Backspace (the latter - contrary to what the online help claims) doesn't tip KeyPress event, so when I fix the value with these, Change is invoked in full. Is there a way to filter them out somehow?



On the other hand, I've been considering other alternatives to emulate the work of such a smart Combo in case a real one isn't possible. For example, I tried to use a simple TextBox to work with the keyboard, almost overlapping the body of a ComboBox except its dropdown button. But when I click on the ComboBox, it's brought forward in Z-order, covering the TextBox completely. And when I try to use ZOrder method to restore TextBox' position, the compiler throws an "Invalid use of property" error. Trying to use ZOrder as a property doesn't work either.

Another possibility would be to decrease the width of the ComboBox, so that only its dropdown button would be visible, and with the TextBox at its left side they both would create an illusion of a single normal-sized ComboBox. That works almost alright, but the dropdown list of the Combo would be appearing to the right from it (if I modify its width for a sensible size, that is) rather than below as if it was belonging to a normal ComboBox. Is it possible to align the dropdown box to the right edge of the Combo body?

Thanks.

 

[SkipVought]

Hi,

"there a way to filter them out somehow?"

Test the KeyANSI value.


Skip,

_[red][/red]

 

[Kimed]

No, no, no, the KeyPress isn't invoked *at all* when these are pressed. That's the problem.

 

[Rofeu]

Hi,

can you not just set the MatchRequired property of the combobox to False? Users can then choose from the list, but also enter new values.

Cheers,

Roel

 

[Kimed]

It's already set to False. The problem is not that I can't enter new values, but that Change event reacts too vehemently, not separating values already in the list (they are used to search for a named set of data as soon as the line is clicked) from values being entered (they are used to create new sets *after* the whole value is entered).

 

[Rofeu]

Hmm, ok. How about testing the MatchFound property?

This will tell you if the value is already in the list. If not, well, you'll never really know if the user is finished typing a new value until he leaves the combobox, so you might use the LostFocus event to trigger the creation of a new set.

Hope this is more along the lines of what you're looking for.

Cheers,

Roel

 

[SkipVought]

You might need an event (button) that indncates, End Of Entry.

Skip,

_[red][/red]

 

[Kimed]

Much better, but if an unfinished value will by accident match an existing one, Change fires prematurely. Ideally it should only react to selecting a value from the list. If the user wants to employ the value for a new set, he presses a button.

Thanks anyway.

 

[Rofeu]

no way of knowing when a user finishes his entry! What if he selects an entry from the list because he knows that his new entry starts with that text???

Use the LostFocus event or a button of some sort that the user needs to press when he's finished.

Cheers,

Roel

 

[combo]

If this does not interfere with other userform controls, you can do the following trick:

Controls:
ComboBox1 with MatchRequired=False
CommandButton1 with Default=True and located outside userform area (just shrink the userform with commandbutton)

Code (this can be extended by any validation):
Private Sub CommandButton1_Click()
With Me.ComboBox1
.AddItem .Value
.ListIndex = -1
End With
End Sub

Hitting the enter key will activate the above code.

combo

 

[rmikesmith]

Kimed,

The following worked for me.

I added a ComboBox to a Userform, keeping the default properties. I created a list of items on a worksheet then referenced it as a named range in the ComboBox's Rowsource property. I added the following two event handlers:

Private Sub ComboBox1_Change()
   With ComboBox1
     If .ListIndex <> -1 Then
       ActiveSheet.Range("A1").Value = .Value
     End If
   End With
End Sub

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  If KeyCode = 13 Then
    ActiveSheet.Range("A1").Value = ComboBox1.Value
  End If
End Sub

For demo purposes I'm simply outputting the results to a worksheet cell. The above setup allowed me either select from the list or add new entries into the ComboBox's text field. If the user is adding a new entry, he signals completion by pressing the Enter key. Note: For this to work, your Userform cannot include a CommandButton having its Default property set to True.

Regards,
Mike

 

[Kimed]

I'm *already* using a button to signal the end of an item that user enters from a keyboard. It's not ergonomic, though, to force him to use the button to signal the value he already "finished" by selecting it from a list. Hence my trying to handle it through Change. I'm striving for an interface not just working but intuitively comfortable - the working one I've got long ago.

Oh well. I guess I can make it to ensure that partial matches do not occur. Thanks everybody, guys.