Userform Combobox - Click event does not fire

[dwooldridge]

This problem has been driving me nuts for the last week. In Excel 2007. We have a userform with a bunch of controls. There are two pairs of combo boxes: SI & STEP are the first pair and PLAN & TASK are the second pair. By pair I mean the user selects an item in the 1st combobox and the 2nd is then populated based upon that selection and they then proceed to select from the 2nd combobox. The 1st pair (SI & STEP) work just fine. The 2nd pair do not.

It's hard to describe but its like the TASK combobox loses its focus or something. To reduce the problem down to a simple form I created a test case:

In the UserForm_Activate method I populate the PLAN combo then the TASK combo. No problems. Now I don't have to click PLAN at all and both boxes are loaded with data. Focus is on PLAN as it's the first tab stop.
I click downarrow on TASK, select an item and we are good to go. So I know this sample case works.

Now just to test my "lost focus" concept I put a MSGBOX statement into the PLAN_Exit event. Now when I click the down arrow on TASK I get the MSGBOX popup. When that closes I get the selection list for TASK and when I choose something (and I can choose) I get nothing in the combo box. If I immediately click the down arrow again and select then I do get a selection.

So you can see why I'm talking about it in terms of "focus" - its like after the MSGBOX the 1st down arrow cycle serves to bring us back to the TASK combobox and it takes a second down arrow to effect real action.

Now that's all fine and good but in my real application I don't have a MSGBOX and for the life of me I can't see that we have lost focus so maybe this "focus" idea is just a crude description of something different.

Sure would love to hear your thoughts as this is driving me nuts.

 

[Norie]

Have tried putting breakpoints on the click events?

That would let you see if they are actually being triggered.

 

[dwooldridge]

Thank you Norie for your reply. I've tried breakpoints. Actually I did a bit better in that I created a tracing system in the userform. In the event routine logic I put calls to the trace code that in turn posts the text to a listbox. In the case of SI & STEP these events fire:

si_enter
si_drop_button_click
si_click
si_drop_button_click
si_beforeupdate
si_afterupdate
si_exit
step_enter
step_drop_button_click
step_click
step_drop_button_click
step_afterupdate
step_exit

In the case of PLAN & TASK the 'step_click' event is missing. We have:

plan_enter
plan_drop_button_click
plan_click
plan_drop_button_click
plan_beforeupdate
plan_afterupdate
plan_exit
task_enter
task_drop_button_click
<------ we should have task_click event right here
task_drop_button_click
task_exit

 

[Norie]

So even when you click the event isn't triggered?

Can you post the code, or, even better, upload your workbook to somewhere like Box.net and post a link to it?

By the way, what events are you using?

From the trace it looks like you are using a fair no of the events available, which might not be a good idea.

 

[dwooldridge]

I"ll see what I can do about uploading code. I had refrained from doing that because it is complex but maybe I can cut the non-relevant stuff. My small scale test case shows that the two combo boxes work and when the MSGBOX comes into play it stops working. Does that seem strange to you? I really don't use MSGBOX except to report a problem so using it here is not really a great simulation of the problem.

And thanks again for your interest in this strange issue.

 

[dwooldridge]

Oh, and by the way I am using a bunch of events but that is temporary. I was doing that just to try and get an understanding of the flow of things. Ya know if would be really cool to have an event list and an explanation of what goes best in each event. I do have the VBA and Macros book from MrExcel Library - an extraordinary book but it doesn't help in this situation.

 

[Norie]

You can find a list of events in the Object Browser, but that doesn't show you the order they are fired in which might be what you are looking for.

 

[dwooldridge]

Firing order is very useful but beyond that. For example:

1. Is it good or bad practice to populate the combo within its own event say the ENTER event?
2. Where is the best place for wrap-up activities: EXIT or CLICK ?
3. How can I change the "value" of the combo without triggering any events?

These kind of questions can go on and on and on.

 

[Norie]

1 It's bad.

2 & 3 really depend on what you are doing.

 

[dwooldridge]

I'm sorry, I didn't mean to waste your time actually answering questions - only to illustrate a "few" questions where it would be great to have answers. But since you mention it I'm wondering why it would be bad to populate the combo box from the Enter event? Historically I have not done it - I just make sure I populated it (or cleared it) in different parts of code within the userform. This might yield 4 or 5 different places where I could trigger the populate activity. But as I thought about it I came to this analogy: Do you get into your car and start the engine or do you start the engine from some other location and then eventually (or never!) get in the car?

In other words do:
I ENTER the car and start the engine or do I start it thinking that at some point I will ENTER the car.
I ENTER the combobox and populate or I populate thinking that at some point I will ENTER the combobox.

But this is maybe philosophical and slightly off track from the original problem which is to get the combobox to drive down the road!