Solved

Questions about filling an Excel combo box...

Posted on 2011-09-06
11
336 Views
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?

Thanks.
0
Comment
Question by:newbieweb
  • 5
  • 4
  • 2
11 Comments
 
LVL 7

Accepted Solution

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

Assisted Solution

by:akoster
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.
0
 

Author Comment

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

0
 

Author Comment

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

Expert Comment

by:BusyMama
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:newbieweb
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!
0
 
LVL 19

Expert Comment

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

Expert Comment

by:BusyMama
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.
0
 

Author Comment

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

Expert Comment

by:BusyMama
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.  ;)
0
 

Author Closing Comment

by:newbieweb
ID: 36496337
Thanks again for the help!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now