VBA Listbox - Mysterious selection after change event

Hi All,

I apologize in advance as this is quite long winded, but not the most straightforward code as a lot has to happen at once. I have been working on some code for the quick selection of a variety of options in different categories. I started to run into a very frustrating problem with ListBoxes where there seems to be some sort of click or selection event that occurs after I click an item, which is calling other routines and initializing code it shouldn't be.

To be more precise, I have attached a sample workbook with one of the Userforms that I'm struggling with. In this Userform, each listbox is populated with values under that specific category from a list in an excel worksheet. The first Listbox shows items that have a common flag in the worksheet, and the last Listbox is meant to store all the selected items. Now my intention is that when a user clicks an item, it disappears from that ListBox (and the common listbox if it's also present there) and appears in the summary listbox. To make matters more complicated, each listbox has a search bar above it, and every time text is entered in the by the user, the listbox changes to only show remaining items that are 'Like' what is typed in. Because of this and the fact that users may want to search for multiple items, the items are all stored in a public array on initialization after being populated to be able to repopulate. This array also contains a flag to know if they've been selected so that they don't re-appear after text is altered in the search box during repopulation. If, however, the item is subsequently removed from the summary listbox, the selected flag is removed and it reappears in the original listbox (I opted to forgo adding it back into the common box after it has been removed).

So hopefully you have an idea of the logic in my code. Anyways, I think all the logic is sound at this point, but I run into this inconsistently consistent problem of what I'll call the 'ghost clicks'. If I click an item in a listbox, it becomes selected and triggers the listbox_change event as expected. This event runs a function which flags the item as selected in the corresponding array, removes it from that listbox +/- the common listbox, and adds to it to the summary listbox. Now in most cases this works perfectly fine, but I get a reproducible error of having not only the item I clicked moving to the summary box, but also the item that will take its position in the box after the intended item has been removed.

Now I know what some of you may be thinking and I found a similar issue in this forum of a 'click through' event, where the original click is still acting once the items have shifted. This was solved with a delay in the listbox to prevent multiple clicks in less than 0.2 seconds. Well I tried that, and even tried 2.2 seconds, but that second 'ghost click' still seems to find its way through. I've tried pauses in the code in case the deselection of the listbox item is taking longer than the code that removes the item (if that is even possible). I've also stepped through the code line by line to try and find what could be triggering this. I even reset the listbox listindex to -1 at the end of my change event but once again, it still finds a way to become selected, run my change event, and move itself over. When I step through line by line, it seems the item that will replace the position of the item that disappeared gets selected AFTER the end sub line of the change event (ie when no code should be running), which re-triggers that event. I thought of putting a timer to prevent the event from re-triggering before a certain time, but that still wouldn't explain why the item is being selected on its own in the first place.

Interestingly, I also added a button that seems out of place, at the top of the userform, which selects an item without me having to physically click it. This seems to prevent the issue as far as I can tell, so I'm guessing it has something to do with the physical click itself (which in my mind should have been corrected by the delay). The 'ghost click' doesn't happen all the time, but I find that during a given session, when I reproduce the same sequence of events in the userform, I consistently can get the 'ghost click' to happen regardless of how short or long I click.

I have spent way too many hours on this so any enlightenment would be greatly appreciated.

I played around with your UserForm and sure enough I could get it to act strange every time. Just hold down the mouse button and then move the mouse over any of the listboxes. Not sure why or how this is happening. My best suggestion is to change your "trigger" code to use the _MouseUp() event instead as that would prevent the extra events from firing I believe. I tried to do this in the attached.

WBD