PDA

View Full Version : Validation source list from another workbook



Sreeja
03-03-2008, 12:05 AM
Hi,
I'm using data validation to create a drop down list in a spreadsheet so that users can select from a pre-defined list. I want to be able to maintain this list in a separate workbook.
Please let me know if it if possible....

Thanks in Advance

tstav
03-03-2008, 01:05 AM
Is that what you mean?
1. In a separate workbook you want to maintain a list of items, e.g.
Item1
Item2
Item3 and so on.
2. In another workbook's worksheet you want to have a combobox (dropdown) that will show the above items and let the user select from them.

If this is what you want to do then the answer is yes. It can be done.

Lets say you keep your items in ItemWbk.xls in Sheet1, in cells A1,A2,A3...
The Worksheet with the combobox is named "WorkS".
You create another Sheet (e.g. "DataS") and bring in it the Items from the ItemWbk.


With DataS
.Range("A1") = "='C:\[ItemWbk.xls]Sheet1'!A1"
.Range("A2") = "='C:\[ItemWbk.xls]Sheet1'!A2"
'and so on
End With


In the WorkS_Activate event you populate your combobox with these values


With DataS
combobox1.Clear
combobox1.AddItem .Range("A1").Value
combobox1.AddItem .Range("A2").Value
'... and so on
End With


Note: I'm picking up the items from your maintenance workbook while it is closed.

Bob Phillips
03-03-2008, 01:54 AM
When you use a list in another workbook, you have to create a name in both workbooks.

The name in the target workbook would be defined to point the list, just a standard name definition.

The name the source workbook would point at the other name, something like

='the other workbook name.xls'!list_name