Solved

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

Posted on 2011-09-08
6
246 Views
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...

Thanks.
0
Comment
Question by:newbieweb
6 Comments
 
LVL 33

Assisted Solution

by:Norie
Norie earned 200 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.
0
 

Author Comment

by:newbieweb
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??




0
 
LVL 33

Assisted Solution

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

Does it constantly change?

Where are you 'downloading' it from?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 6

Assisted Solution

by:reitzen
reitzen earned 100 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.
0
 
LVL 41

Accepted Solution

by:
dlmille earned 200 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

Enjoy!

Dave
0
 

Author Closing Comment

by:newbieweb
ID: 36524137
Thanks!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Outlook Free & Paid Tools
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now