What VBA event should I use to auto populate a combo box?

Posted on 2011-09-08
Medium Priority
Last Modified: 2012-05-12
I wrote the code to download data from a database, to fill the Named Ranges used by ComboBoxes. I want this download code called often, but not just before they open the ComboBox, since that will cause a delay (I think).

I would like to find that event so I can do a performance test, since that's the best time to get the freshest data, as they open the ComboBox.

Secondly, I need another event I can respond to where I could put this code to dowload the data, as an alternative.

What can you suggest?? Opening the Combo is the first I should try.

I am new to coding in Excel.

Please let me know...

Question by:newbieweb
LVL 37

Assisted Solution

Norie earned 800 total points
ID: 36505987
You shouldn't call the code often, you should only  call it when you need to populate the comboboxes.

You also shouldn't call it from one of the events of the combobox itself.

Where are these comboboxes anyway?

You've not said here, or elsewhere.

Author Comment

ID: 36506111
The combo boxes contain the items downloaded from the database. I have dozens of tables and dozens of combos.

The question is, what event do I call?

I may download from all tables every 15 minutes, for example. That could work if I had a global variable and checked the clock time, and used an event that was called often like SelectionChange.

Any better ideas??

LVL 37

Assisted Solution

Norie earned 800 total points
ID: 36506442
Why are you downloading data every 15 minutes?

Does it constantly change?

Where are you 'downloading' it from?
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.


Assisted Solution

reitzen earned 400 total points
ID: 36508213
If your combo is in a worksheet, you could refresh the data using the Activate event for that sheet.  If it is in a form, you could use the UserForm_Initialize event.

For the user experience, you might want to allow the user to decide when they want to get the "freshest" data.  I'm not sure what your workbook is going to be used for, but if the time to connect, request, and receive the data is more than a few seconds your users will not be happy.  It will prevent them from doing anything in the workbook, similarly to the Auto Save feature.  You know how annoying this can be if your workbook is sizable.
LVL 42

Accepted Solution

dlmille earned 800 total points
ID: 36508240
I load comboboxes on demand all the time, sometimes with a few thousand records.  MAYBE a 2 second delay...

I don't recommend Selection_Change.  Like imnorie, you shouldn't call it more often than you need to (unless your delay is not good).  

It stands to reason that the actual EVENT to reload the ComboBox should be the DownloadData event.  How are you making the DownloadData process happen?  If with VBA, just add the populate ComboBox to that.

If you're doing it with some other type of refresh, please advise us as to what type of refresh that is - take a screenshot so we can see what you're doing - or just advise - is it a Web Refresh, a PivotTable (tied to database) refresh, a Database Query refresh (any of these instigated by the user can be managed, but with some sophisticated code).

However, if YOUR VBA is initiating it - e.g., an OnTime Event running a macro, scheduled for 15 minute intervals, then you could do this:

Just refresh the combobox, or, alternatively:

In the macro, check to see if the user is on the same TAB as the ComboBox - if so, then load the ComboBox for the user.

If not, raise a global boolean variable to advise that the ComboBox has been refreshed.

Then, on the Worksheet_Activate event in ThisWorkbook's codepage, just check that flag - if true, then set it to false, and update the ComboBox.

In this fashion, the combobox would load just before it might be needed.

How should you load your combobox?  You have two alternatives:

Record by record - has the highest delay
Pointing the ComboBox to a dynamic array using the listFillRange property - probably as close to instantaneous (exaggerating a bit).
So, your DownLoadData VBA code would just be populating a spreadsheet range.  A dynamic range is created and assigned a range name.  Your combobox's listFillRange is set to that dynamic array.


PS - If you want to populate the combobox with a unique set of values (it can be more involved) then you might be very interested in these two articles (much, if not almost all the work is already done for you - and it talks about different approaches that can be taken to populate, though the default is on-demand.  I'm happy to help you through it if you want to go this way.  

PS - if you like the articles, please mark "Yes".

The first is a bit technical on how its done: http:/A_5062.html,
but the code was generalized a bit (better) and offered as a rewrite for the second article,  http:/A_6429.html



Author Closing Comment

ID: 36524137

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
I came across an unsolved Outlook issue and here is my solution.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question