Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-09-08
6
Medium Priority
?
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 35

Assisted Solution

by:Norie
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.
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 35

Assisted Solution

by:Norie
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?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 6

Assisted Solution

by:reitzen
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.
0
 
LVL 42

Accepted Solution

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

Enjoy!

Dave
0
 

Author Closing Comment

by:newbieweb
ID: 36524137
Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

610 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