UserForm Event Fires Randomly

[Boswell]

I have a workbook with a rather complicated series of user forms. One of the forms consists of a list box with an associated click event that fires randomly (or for reasons i do not understand). I have tirelessly stepped through the code to verify that the list box is *not* being "clicked" or altered programmatically when it is firing.

The list box click event tends to fire when a range on a worksheet is calculated. Additionally, it fires 20+ times upon the user form initialization event. I set the contents of the list box via row source and even if there are only a few items added upon initialization the list box click event still fires 20+ times.

Is anyone familiar with this problem.. are there some coding best practices I am overlooking? The code is too circuitous to post; however, I am an experienced VBA programmer and have ruled out any obvious issues (such as nested events that are calling each other back and forth)

Any suggestions would be appreciated.

 

[Hermanito]

when the sheet is recalculating, does this include the cells you use for rowsource of the listbox? Then each recalculate might trigger the change event, even if the value does not really change. Not sure here, but it must be something like that if you ruled out other things...

 

[Boswell]

Thanks for your reply Hermanito. That is a very good thought I had not considered. I do not think that this is the issue, however I am going to step through the code again to be absolutely sure.

 

[RoryA]

If you have controls bound to the worksheet, as Hermanito said, that is almost certainly the cause. It's one reason why I never bind anything directly to a worksheet (either in or out).

 

[Hermanito]

I tried to reproduce this behaviour with a simple setup:
- userform with listbox bound to a range
- bound range contains formulas
- other cell contains counter of how many times the listbox change event gets called

the listbox on the (modeless) userform gets updated when I recalculate and the formula results change, but the listbox change event does NOT fire in my case...

I did not expect that actually...

 

[Hermanito]

Previous test was with the userform shown, but without selecting anything in the list.
When I have a listitem selected, and then change values in the bound range, the change event gets called twice
Then I thought, maybe when multiselect is enabled and I select everything in the list, the change event gets called twice for each element, but that's not happening. In that case, it doesn't fire at all again...

 

[RoryA]

For a multiselect listbox, the Value property no longer returns anything, so it doesn't change.

 

[Boswell]

I too have not been able to slow my click events by altering my code, but have a mountain of it to go through to weed out all possible instances where I am calculating the range containing the row source. I am warming up to the idea that this is indeed my issue...

Note that My issue is with a click event (not a change event).

 

[RoryA]

Personally, I would remove the rowsource and populate the list in code using:

listbox1.list = sheets("blah").Range("yadda").Value

 

[Hermanito]

Ah, I totally overlooked that you were talking about the click event. That makes it even weirder I think...

Have you tried closing and reopening Excel?
If that doesn't work, rebooting the pc?
(just stating the first two options any helpdesk would provide )