Linking Combo Boxes from different Worksheet in an Excel Workbook

0
votes

I have a workbook with Combo Boxes on each Worksheet that are linked to the same data range. To help prevent bad data entry I would like to link Combo Boxes with the Same Data. For example each worksheet has a Country combo box. When the Country is selected on Sheet1 I want it to set the Country selection on all subsequent worksheets with a Country combo box.

I'm working with Excel 2007. Any suggestions or assistance would be greatly appreciated. I can get them to update with the selected field but they lose all the other data ie: the other options pulled in from the range of Countries.

Cheers.

1
excelexcel-2007vba
I'm not sure I see any programming question here. If it's a VBA question, please edit your question and post the code you've tried so far that isn't working for you and explain in what way it's not working. If this is a general Excel question not related to VBA, it belongs on SuperUser instead. – Ken White
@KenWhite - I agree about the VBA - but cell expressions in Excel are programming too I would say. But in this case I'd say that the author is looking for VBA. – Preet Sangha
@Preet, perhaps. However, there is no code of any sort here, whether it's VBA or Excel cell expression. This isn't a tutorial site on how to use Excel, which is why I provided a reference to SuperUser. – Ken White
@JasonR: I agree with KenWhite. I am not sure what exactly your query is. Perhaps you would like to attach the sample data or snapshots and show us what exactly is the problem? – Siddharth Rout

1 Answers

0
votes

When your question is how can I select a country from a combobox in sheet1 and use the same value in all the other sheets in the same workbook, the answer is to reference the cells in the other sheets to the cell in sheet1 where the selection was made. In other words remove the country selection comboboxes from all sheets EXCEPT the first one and let the selected country on the first sheet drive the data on the other sheets (e.g. sheet1$A$1 or even better use a defined name!).

Alternatively, you could create a VBA script (on_change) for every sheet which is executed when the country code cell is changed on that sheet. The script reads the latest selected country, then puts the selected country value in e.g. sheet1$A$1. You would still reference all other sheets from this single controlling cell ($A$1) though.