Questions about filling an Excel combo box...

Posted on 2011-09-06
Medium Priority
Last Modified: 2012-05-12
I need to move the settings page for multiple combox boxes away from an Excel spread sheet (Office 2007) and into a distinct Excel file. Right now we have a "Selections" page but I need to replace that with an external data source.

1) How do I move the Selections page to a distinct Excel file, XML file, CSV file, etc?
2) How do I then populate that list from a database?
3) How do I find the place in the Excel Workbook where the a given selection is already referenced by a combo box?

Question by:newbieweb
  • 5
  • 4
  • 2

Accepted Solution

BusyMama earned 1400 total points
ID: 36495091
Good Morning-

If you are literally looking for "how to" on a couple of these ...

1)  Move the Selections page by right-clicking on the Selections tab (the tab name, where it says Selections", choose "Move or Copy", and select "New book" using the drop-down arrow in the top of the dialog box.  Then do a "Save as" to save the file.

2)  Can you be more specific - do you want the list to be dynamically connected to the database, or do you just want to copy and paste a list into the Excel sheet?

3)  To find the place in the Excel workbook which is referenced by the combo box, make sure you are in Design Mode.  Right-click on the combo box and choose "Format Control".  On the Control tab you should see what is filling the combo box.

I hope that helps to get you started at least.
LVL 19

Assisted Solution

by:Arno Koster
Arno Koster earned 600 total points
ID: 36495164
1) in general, you can copy the sheet to a new workbook, and save it as a new file. When you have verified the correct behavior you can delete the sheet from the old workbook. Alsways keep backup copies, just in case !

2) assuming that you keep the distinct excel file with the selections sheet open AT ALL TIMES, you can use this vba macro to do the trick.
this sets the data source to the selection range and only has to be run once to update the data source.
Sub update_combo_data_source()
ComboBox1.ListFillRange = "[name_of_the_distinct_selections_sheet_excel_file.xlsx]Sheet1!name_of_the_selection_range"
End Sub

Open in new window

You'd have to replace the name of the excel file and the selection range to match your situation of course.

3) when you enable the design mode in the developers menu, you can right-click on a combobox and have its properties displayed. Look for the value of the property 'ListFillRange'. This should be the named reference to the given selection.
Then, type in this reference name in the name box which normally displays the address of the currently selected cell, and press enter.
This should bring you to the worksheet which holds the named reference and have the cells selected as well.

Author Comment

ID: 36495354
Thanks to you both for excellent information.

I feel sure that I will be able to use an external data source through one of the various methods you've described.

> 2)  Can you be more specific - do you want the list to be dynamically connected to the database, or do you just want to copy and paste a list into the Excel sheet?

I do want real-time access to a database which will contain the drop-downs. That's our ultimate goal. But I needed the alternate Excel file as our "offline" solution, needed as a result of the hurricane.

Thanks for all the help!


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

ID: 36495449
I just read:

> assuming that you keep the distinct excel file with the selections sheet open AT ALL TIMES, you can use this vba macro to do the trick.

Does this mean in order for me to reference the external Excel page, that page must always be open in Excel?

This may be a show stopper, forcing me to look for another way to persist the data locally (XML?) and NOT need another instance of Excel open.

Please let me know...

Expert Comment

ID: 36495508
I would use the Excel file as the master for the database, instead of updating the Excel file FROM the database.  Keep all the necessary values in the Excel file and in the database, link the table to the Excel file.

As far as akoster's comment, I do think you have to have the sheet open for it to work in the combo box, but maybe he can come up with something for you that will prevent that.

Author Comment

ID: 36495618
>  instead of updating the Excel file FROM the database

We use Excel for data entry and have an Oracle database where the value reside, managed by the client. So we need to have the values cascade down from database to Excel.

>  link the table to the Excel file.

That's a great idea. Linking the table to Excel.

So, when the user is onlline, the values will be refreshed. But when they are offline, the old values will still be in the same exact spot as when they are online.

Do I understand this correctly?

Thanks again!
LVL 19

Expert Comment

by:Arno Koster
ID: 36495639
If you use the inner-excel-link as proposed, indeed it will only work when the file is opened. it may be minimised though and the sheet with the selection ranges can be hidden.

It might be best to include a hidden worksheet to each workbook containing dropdown boxes. When necessary, you could write a small macro code to update the selection data when opening this workbook.

Expert Comment

ID: 36496093
At my old job we used Excel for many of our "master" files which we wanted to give the users more control over.  They could update the Excel values, then we (Oracle administrators) had routines to pick up the files and load them into Oracle (either when they changed or on a scheduled basis, depending on the need).

So yes, I believe that would work for you, keeping Oracle updated for 'online' work and Excel for 'offline'.

I agree with akoster's idea to update the selection data (also from the "master" Excel document) when opening the workbook with a hidden sheet.

Author Comment

ID: 36496140
The Oracle database is owned bythe client. We have read permission only. I want to use the Oracle database (when it's available and the user has selected to Work Online) to update Excel. Otherwise, I want the data to get stale in Excel.

Expert Comment

ID: 36496303
Gotcha'.  I think you're already getting some ideas on linking Excel to Oracle in another thread, so I'm staying out of that one.  ;)

Author Closing Comment

ID: 36496337
Thanks again for the help!

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
In this article, I will demonstrate that how to do a PST migration from Exchange Server to Office 365. This method allows importing one single PST, or multiple PST's at once.
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.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

571 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