Solved

Questions about filling an Excel combo box...

Posted on 2011-09-06
11
337 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Office.Com /myAccount 9 40
Macro 6 49
Excel Formula 4 28
Excel sheet question 12 22
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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…

911 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

20 Experts available now in Live!

Get 1:1 Help Now