Combo box Change Event challenge

[fprzekop]

I want to run VBA code when a user either clicks or presses enter to select a value from a list in a combo box control. Sound simple... right?

I've tried using the combobox_change event, but if the user starts typing a partial string for a value in the comboBox list, the Change event fires BEFORE the entry is complete. (Example: user starts typing first character (1) for a list item 12345 and the change event fires before he/she is done causing havoc!) If the user arrows down, change event fires as well!

So then I tried using a worksheet_change event hoping that changes to the combo_box linked cell would "Fire" the event. Not so. User can select an item from the combo box, linked cell updates, but worksheet_change event does not get triggered. ANY ADVICE APPRECIATED. Thanks

 

[logicmachine]

I want to run VBA code when a user either clicks or presses enter to select a value from a list in a combo box control. Sound simple... right?

I've tried using the combobox_change event, but if the user starts typing a partial string for a value in the comboBox list, the Change event fires BEFORE the entry is complete. (Example: user starts typing first character (1) for a list item 12345 and the change event fires before he/she is done causing havoc!) If the user arrows down, change event fires as well!

So then I tried using a worksheet_change event hoping that changes to the combo_box linked cell would "Fire" the event. Not so. User can select an item from the combo box, linked cell updates, but worksheet_change event does not get triggered. ANY ADVICE APPRECIATED. Thanks

I'm having the exact same problem. Can anybody please suggest a solution? Thanks

 

[Gary McMaster]

I've had good luck with the "KeyDown" event. You can also use select case for the "KeyCode" if you need more than one.

Gary

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = 13 Then '13 = Enter / Return

    'Your code
    
End If

End Sub

 

[logicmachine]

I've had good luck with the "KeyDown" event. You can also use select case for the "KeyCode" if you need more than one.

Gary

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
 
If KeyCode = 13 Then '13 = Enter / Return
 
    'Your code
 
End If
 
End Sub

Thanks Gary. I actually am already using some code inside the keydown event to allow a user to add a value to a combobox if it isn't already in there by typing the value and pressing the Enter key. (I've done my Mrexcel.com research ) I am also using the click event to let the user choose an already existing value from the combobox drop-down list, which is causing the problem:

As soon as somebody types even 1 character into the combobox, it activates the click event before reaching the line in the keydown event to check if the Enter key was pressed. I thought the click event was only supposed to be activated if somebody clicks on a value in the combobox's drop-down menu, but apparently not. Is there a better event to use than the click event?

Thanks

Edit:
By the way, I am using comboboxes inserted in worksheets from the controls toolbox, rather than the forms toolbar.

 

[RoryA]

The Click event will be triggered if the typed text matches an entry in the list (assuming the combo is set to match entries as typed).
Do you particularly need a combobox control rather than a DV dropdown? If so, does it have to be an ActiveX one?

 

[logicmachine]

The Click event will be triggered if the typed text matches an entry in the list (assuming the combo is set to match entries as typed).
Do you particularly need a combobox control rather than a DV dropdown? If so, does it have to be an ActiveX one?

Thanks Rory, you solved my problem! I set the MatchEntry property in the properties window to 2 - fmMatchEntryNone, and now the Click event is no longer activated when typing in the combobox. I'm sure glad I found this website.

 

[RoryA]

Glad to help.