UserForm.ListBox_Change event not working as expected

[AdLoki]

Hi All,

I have a VBA UserForm GUI and one of the controls is a multi-select, tick-box option ListBox. The items in the listbox are either category types ("Domain1", "Domain2", etc) or items that fall in said categories ("Indicator1", etc..).

I have written a macro to handle the different ways in which the options can be checked - for instance, if a category type is checked, all of the elements in that category will also be checked. Additionally, there is cross-category grouping such that once a list item has been selected, only items from the same group (which could be in a different category) are displayed.

My problem is that my macro seems to work as I would like but as soon as the code returns to the ListBox_Change declaration that calls it, it seems to change the items in the list, despite there being no code instructing it to do so. This doesn't happen in breakpoint mode.

I attach an example workbook - if you launch the UserForm and select DOMAIN1, it correctly checks the members of the DOMAIN1 category and removes from the list all items that aren't in Group 1. The problem arises if you then try to make any further changes to the list: for instance, if you then uncheck Indicator1, it doesn't change anything - it briefly flashes to the correct list but then reverts to the previous list. However, if you put a breakpoint in after the macro is called (in the ListBox_Change declaration) there is no problem.

It seems that the ListBox_Change declaration itself is making changes to the list and I don't understand why - does anyone have any ideas?

FYI I have tried the following with no improvement:

1. Looped through the list and deselected all items before clearing
2. Tried using the MouseUp event (this had similar problems)
3. Putting a Sleep in at the end of the macro in case there was some lag
4. Adding in DoEvents everywhere

PS: I know that my macro is not perfect, but I don't think that it is the problem per se - I won't be able to finish refining the code until I can effectively test it. My suspicion is that it has something to do with programmatically selecting/deselecting list items but I can't be sure.

[AdLoki]

Sorry to bump this but I have a looming deadline on the project and this is eating up so much of my time without being resolved - any help would be hugely appreciated.

[romperstomper]

It seems to be a timing issue. Adding DoEvents before the call to ChangeRAGIndList resolved it for me.

[AdLoki]

Sadly this didn't seem to make any difference to me, it is still behaving in the same way... I can't say I've ever found DoEvents to make any positive differences but I was so hopeful when I saw your message! Thanks for trying...

[AdLoki]

Does anyone have any suggestions for either:

1. What an alternative solution could be?
   or
2. Why DoEvents works for romperstomper but not for me?

Any thoughts would really be appreciated!

[romperstomper]

Which version of Excel and Windows are you running, so I can test the same environment?

Edit: testing on a different computer today, DoEvents doesn't fix it. I still think it's a timing issue to do with changing the list while the click is taking place, since this does seem to work:

Please Login or Register  to view this content.

[romperstomper]

It's flickery, but this also seems to work here:

Please Login or Register  to view this content.

[AdLoki]

Thanks, I'll give that a go later on.

FYI I'm running in Excel 2013 32bit on a 64bit Windows 8.1 platform with 8Gb of RAM. That said, this will be part of a commercial application so it needs to run on Excel 2007 and later on low spec machines.

[AdLoki]

Hmm, just tried your solution - switching the visibility of the control didn't make any difference and adding in the delay does work but not consistently. It's a really odd one and I wish there were a reliable solution but given that this is going to be used by a variety of users with different setups, I think I'm going to have find an alternative to the listbox idea for my tool because this is simply too unstable to be of real use. Thanks for your suggestions though, one day I'll actually get my head around C# and build my tools in a language that is more dynamic!

[romperstomper]

It does look like a Treeview control would be more appropriate here.