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
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
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.
[vba]
With DataS
.Range("A1") = "='C:\[ItemWbk.xls]Sheet1'!A1"
.Range("A2") = "='C:\[ItemWbk.xls]Sheet1'!A2"
'and so on
End With
[/vba]
In the WorkS_Activate event you populate your combobox with these values
[vba]
With DataS
combobox1.Clear
combobox1.AddItem .Range("A1").Value
combobox1.AddItem .Range("A2").Value
'... and so on
End With
[/vba]
Note: I'm picking up the items from your maintenance workbook while it is closed.
He didn't know it was impossible, so he did it. (Jean Cocteau)
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
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber