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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.