Solved

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

Posted on 2011-09-08
6
255 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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 42

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

791 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