?
Solved

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

Posted on 2011-09-08
6
Medium Priority
?
261 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 34

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 34

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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

752 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