Questions about filling an Excel combo box...

Posted on 2011-09-06
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 350 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

akoster earned 150 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!

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.


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

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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
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…

803 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