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

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
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?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
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!
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
85,042
Office Version
  1. 365
Platform
  1. Windows
ActiveX controls can bit a flaky, try a Forms Control listbox instead.
 
Upvote 0

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
16,545
Office Version
  1. 2013
Platform
  1. Windows
ADVERTISEMENT
To refresh the update continuously you can use the DoEvents instruction
 
Upvote 0

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
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.
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
85,042
Office Version
  1. 365
Platform
  1. Windows
ADVERTISEMENT
How is the listbox populated?
 
Upvote 0

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
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:
Code:
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.
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
85,042
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
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.
 
Upvote 0

Forum statistics

Threads
1,195,625
Messages
6,010,757
Members
441,568
Latest member
abbyabby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top