What event is triggered when you click on a combo's drop down list?

[Leon Lai]

Hello,

I have a UserForm with a combobox.

When I click on any item in the drop-down list, the item goes in the combo box.

I use the combo_change event because I do search-as-you-type.

What combo event is triggered when I click on an item in the drop-down list?

I tried combo_click event. Is it good?

I want to disable the combo_change event when I click on an item on the drop_down list.
But it does not work.

The problem is this:
The moment I click on an item in the drop-down list, it goes into the combo. This triggers the combo_change.
As I am implementing Search-As-You-Type, a drop down list appears below the combo, with a duplicate of the text on the combo. Very annoying!

I tried to use combo_click event to disable combo_change, but it seems that combo_click is faster!

So, what event can I use to capture the moment AFTER I click on the dropdown, but BEFORE the item is copied to the combo?

Does such an event exist? Any walk around?

Any help much appreciated.

Thanks
Leon Lai

[HansV]

Welcome to Eileen's Lounge!

The Change event always occurs before the Click event, so there's no point in trying to disable the Change event from the Click event.

When the user displays or collapses the drop-down list, the DropButtonClick event of the combo box occurs.

When the user displays the drop-down list and selects an item from the list, the following events occur, in this order:

DropButtonClick
Change
Click
DropButtonClick

[Leon Lai]

Hello HansV

Thanks a lot for your very insightful answer.
I spent many days trying to disable the Change Event from the Click Event.
Thanks for avoiding me to continue banging my head against the wall.

Why did you list DropButtonList twice: once top priority, the other lowest priority?

--------

Is there any way I can solve my problem, which is as follows:
When I have clicked on the Combo's List, the selected item appears on the combo, and this triggers the Combo's list to appear below. How can we hide the List?

------
When I press ENTER, ESCAPE, DOWN, or TAB on the keyboard, the Combo's List disappears: which is what I want.
-----

So, I tried to use Sendkeys to simulate the above key presses, but none of them work!
But other sendkeys, like LEFT, RIGHT or typing a string all work correctly in their context.

Why does pressing ESCAPE manually work, but doing it by code (with Sendkeys) does not?

Thanks
Leon Lai

[HansV]

> Why did you list DropButtonList twice: once top priority, the other lowest priority?

The user clicks the drop-down arrow or presses Alt+Down Arrow on the keyboard: the BropButtonClick event occurs
The user select an item: first the Change event occurs and then the Click event.
The drop-down list collapses: the DropButtonClick event fires again.

I don't understand why selecting an item would cause the drop-down list to be displayed again. Do you have other code that might influence this?

[Leon Lai]

>> why selecting an item would cause the drop-down list to be displayed again?

I am developing an Excel add-in which uses Search-as-you-type.

The combo's List box is populated by SQL Query (Stored Procedure)

Whatever you type in the combo will be searched in the Combo's List Box as and when you type.
This is made possible by the use of Combo_change event.

Suppose you have found the item you wanted. You click on it (in the combo's drop down list).

What happens?
Well, the selected item goes in the combo box. And THIS FIRES THE COMBO_CHANGE EVENT AGAIN!

My add-in looks for the item, and of course it finds it.

So, the item is displayed twice:
Once - in the combo box.
2nd - in the Combo's drop-down list below it.

This is quite annoying.
So, I want to hide the drop-down list (by VBA)

My only hope is sendkeys to simulate ESCAPE, ENTER, etc (all these make the drop-down list disappear).

Alas! this does not work!

[HansV]

Without seeing the code it's hard to suggest something, but you might experiment with a variable of type Boolean defined at the top of the userform module, say GetOut.
Initially, this variable will be False.
Use it like this:

Code: Select all

Private Sub MyCombo_Change()
    ' Check the variable
    If GetOut = True Then
        ' If it's True, then reset it and get out to prevent the code below from running again
        GetOut = False
        Exit Sub
    End If
    ' Your code here
    ...
    ' Set the variable to True for the next time the event fires
    GetOut = True
End Sub

I haven't actually tested this, so as I mentioned above: experiment!

[Leon Lai]

> Without seeing the code it's hard to suggest something
Agree!
My code is very long and complex, with lots of comments.

So. I'll give a very high-level overview.

My add-in works as follows:
(1) It works on any (appropriate) open workbook.
---
(2) When a workbook is opened, it triggers a Stored Procedure which fetches a list of Stock Items from my SQL Server, and copies it to "Sheet1".
---
(3) It copies the Excel range first to a 2-d array, then to a 1-d array (Call it Array1).
---
(4) I use 2 tricks to implement Search-As-You-Type so that anything that I type in the combo is listed in the combo's drop-down list.
Suppose I am searching for "Lemon".
I type "L" and the drop down displays 2000 items containing "L'
I type "E" and the drop down now displays 500 items containing "LE"
I type "M" and the drop down now dsplays 4 items containing "LEM"

Ah! I see "Green Lemons" which is what I want,

I click on it and up it goes in the combo, ready for my next action button.
----
(4a) The first trick that I use is to create a COMBO_CLICK EVENT which responds each time I type something in the combo.

UNFORTUNATELY, it also responds when I click on "Green Lemons". As this item goes in the combo, it fires the event again, and "Green Lemon" appears in the drop-down list.
-----

(4b) The 2nd trick that I use is to FILTER Array1 according to sMatch (which is the string I am typing in the combo)

sMatch = combo.Value
Array2 = Filter(Array1, sMatch, True, vbTextCompare)
Me.combo.List = Array2
----
Using the above techniques, I can design a very powerful and super-rapid search engine.

====================================================================

>> you might experiment with a variable of type Boolean defined at the top of the userform module, say GetOut.

Yes, I am precisely working on this.

BUT I am not sure at what points I should ENABLE GetOut and DISABLE GetOut.

As you said, I must experiment....

MY PROBLEM IS THAT COMBO_CHANGE EVENT FIRES IN 2 DIFFERENT SITUATIONS:
(1) Each time I type something in the combo box
(2) When I select my required item in the drop-down list and it goes into the combobox.

I WANT TO DISABLE (2) BUT NOT (1). Problem!!!!
HOW TO DO THIS?
WHERE TO SET GetOut = True or False????
HOW TO DISTINGUISH (2) FROM (1).
I tried to use combo_click for (2), but you told me I cannot use it to disable combo_change?

=======================

BUT THERE IS ONE QUESTION you don't seem to have considerd:
Once I have clicked on "Green Lemons" on the drop-down list, up it goes in the combo box.

But this immediately fires the Combo_change, and my add-in searches for "Green Lemons", finds it, and displays it in the drop-down list. NASTY!

I just need to press ESC on

Picture1.png

keyboard to make it disappear.

Why SENDKEYS does not work?

I feel Sendkeys will be an easier solution than GetOut, but how can I make it work???

BTW, I discovered that ONE Sendkey DOES WORK! It's F10. Curious?
I don't want to use it because it creates a minor problem. It acts like a toggle button!
---------
Best Regards
Leon

Picture1.png

Picture 2.png

Picture 3.png

[HansV]

Would it be better to use the KeyPress event (or perhaps the KeyDown event) of the combo box instead of the Change event to execute the search-as-you-type code?

[Leon Lai]

I don' t know what you have in mind.
Are you thinking of using keydown event to do Search as you type, and reserving the combo change event to detect copying the selected item from dropdown to combo?

Kindly expose your idea.

I will try

[HansV]

Yes, that's what I had in mind - that way, selecting an item still triggers the Change event, but that won't cause the search to start again.

[Leon Lai]

Thanks a lot for your support.

I will work on your idea during the week-end.

If I can exploit two different events, which don't conflict, that would be ideal!

Best Regards,

Leon