ActiveX Listbox - Items disappear after click while code is running (then reappear when complete)

[bs0d]

I have an ActiveX listbox on my sheet. I have a number of sub routines that run once the user clicks an item in the list. My issue is that once a user clicks an item in the list, the listbox appears empty briefly while the code is running, and the items show back up when the code is done. This takes about a full second. In a perfect world, the listbox does nothing while the code is fetching data. Does anyone have experience resolving this behavior?

 

[DanteAmor]

could you put the code

 

[bs0d]

could you put the code

There is a lot of code that is executed that makes it unreasonable to post it all. I'm ok with discussing this from a high level, if others experience this when doing x, y or z; and how they resolved the behavior.

I've tried executing the code without screen updating being turned off, and setting calculation to manual, still no luck. I understand with the amount of code being executed that Excel may take a second to complete the request but id like to avoid the listbox going blank temporarily if possible. Thanks again!

 

[Fluff]

ActiveX controls can bit a flaky, try a Forms Control listbox instead.

 

[DanteAmor]

To refresh the update continuously you can use the DoEvents instruction

 

[bs0d]

I also tried DoEvents within the click sub of listbox. This didn't resolve the issue. I have a feeling Fluff is correct. I'm going to switch away from an ActiveX control and see if the Form Control behaves more nicely. However, I'd still like to hear ideas about possible solutions.

 

[Fluff]

How is the listbox populated?

 

[bs0d]

I have a combo box in a ribbon, that when an item is selected from that, a query is executed to a local access database, and those results populate the listbox in the sheet.
This is done using the .list property of the listbox, like this:

Sheets("Sheet1").lstMylistbox.List = Application.Transpose(myResults.GetRows)

Afterwards, if a listbox item is clicked, the data related to that item is queried from the same database and returned to the workbook.

 

[Fluff]

If you're pulling the values straight from the database, that maybe the reason.
Might be worth writing the data to a sheet & then populate the listbox from there, to see if you still get the problem.

 

[bs0d]

Note that my issue is after the listbox is populated. The list disappears temporarily when a user clicks an item that is in the list (already populated from DB). Once the database request is complete, the list reappears. No changes to the listbox itself occur in the _click event.

The temporary list disappear behavior seems similar to when Excel is freezing up and the application sort of grays out, then clears up when the program returns to a responsive status.