Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox external source
In the first sheet of my file I have a cell (C3) that accepts input (a
Supplier code) to be validated against an external Excel file table/range (Column A). Then C4 displays the Supplier name (Column B). I would like to have this done by a ListBox in order to guide the user to all available choices. Please help me set it up since I have never used User forms (???) and such Control objects in VBA and I have trouble initializing them. I found in the Help system sample code using a user form but I don't use User Forms, not to my knowledge anyway. Please bear in mind that the table is external. Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox external source
Assume your table/range is in SupplierList.xls on sheet Data, Range:
"A1:B100" In your sheet, Select C3 then do Data=Validation. You would need to use the List option and set it to =List1 but do this after you create these names: after you create a defined name Insert=Name=Define Name: List1 RefersTo: =[SupplierList.xls]Data!$A$1:$A$100 and another Insert=Name=Define Name: = List2 Refersto: =[SupplierList.xls]Data!$A$1:$B$100 Then in C4 you would have =if(C3="","",Vlookup(C3,List2,2,False)) SupplierList.xls would need to be open when you use the workbook with these formulas (at least for the Data=validation to work). -- Regards, Tom Ogilvy "DoctorG" wrote in message ... In the first sheet of my file I have a cell (C3) that accepts input (a Supplier code) to be validated against an external Excel file table/range (Column A). Then C4 displays the Supplier name (Column B). I would like to have this done by a ListBox in order to guide the user to all available choices. Please help me set it up since I have never used User forms (???) and such Control objects in VBA and I have trouble initializing them. I found in the Help system sample code using a user form but I don't use User Forms, not to my knowledge anyway. Please bear in mind that the table is external. Thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox external source
Hello Tom,
1. I have the same problem, except that on the name of the xls file there are spaces 'like "this excel sheet.xls". And this seems to be refused by Excel. How to solve this ? 2. Can we imagine (in term of performance), having a list of 6000 items coming from another workbook ? Thanks for your valuable help. Francois. "Tom Ogilvy" wrote: Assume your table/range is in SupplierList.xls on sheet Data, Range: "A1:B100" In your sheet, Select C3 then do Data=Validation. You would need to use the List option and set it to =List1 but do this after you create these names: after you create a defined name Insert=Name=Define Name: List1 RefersTo: =[SupplierList.xls]Data!$A$1:$A$100 and another Insert=Name=Define Name: = List2 Refersto: =[SupplierList.xls]Data!$A$1:$B$100 Then in C4 you would have =if(C3="","",Vlookup(C3,List2,2,False)) SupplierList.xls would need to be open when you use the workbook with these formulas (at least for the Data=validation to work). -- Regards, Tom Ogilvy "DoctorG" wrote in message ... In the first sheet of my file I have a cell (C3) that accepts input (a Supplier code) to be validated against an external Excel file table/range (Column A). Then C4 displays the Supplier name (Column B). I would like to have this done by a ListBox in order to guide the user to all available choices. Please help me set it up since I have never used User forms (???) and such Control objects in VBA and I have trouble initializing them. I found in the Help system sample code using a user form but I don't use User Forms, not to my knowledge anyway. Please bear in mind that the table is external. Thanks in advance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox external source
what if supplierlist.xls is not open? is there a way to access the data
validation drop down list without opening supplierlist.xls?or any way to automatically access them in background? "Tom Ogilvy" wrote: Assume your table/range is in SupplierList.xls on sheet Data, Range: "A1:B100" In your sheet, Select C3 then do Data=Validation. You would need to use the List option and set it to =List1 but do this after you create these names: after you create a defined name Insert=Name=Define Name: List1 RefersTo: =[SupplierList.xls]Data!$A$1:$A$100 and another Insert=Name=Define Name: = List2 Refersto: =[SupplierList.xls]Data!$A$1:$B$100 Then in C4 you would have =if(C3="","",Vlookup(C3,List2,2,False)) SupplierList.xls would need to be open when you use the workbook with these formulas (at least for the Data=validation to work). -- Regards, Tom Ogilvy "DoctorG" wrote in message ... In the first sheet of my file I have a cell (C3) that accepts input (a Supplier code) to be validated against an external Excel file table/range (Column A). Then C4 displays the Supplier name (Column B). I would like to have this done by a ListBox in order to guide the user to all available choices. Please help me set it up since I have never used User forms (???) and such Control objects in VBA and I have trouble initializing them. I found in the Help system sample code using a user form but I don't use User Forms, not to my knowledge anyway. Please bear in mind that the table is external. Thanks in advance |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox external source
To set up a list for users to select from, on the menu bar, click on "Data". Then "Validation". Under the settings tab in the "Allow" window select "List". Under "Source" enter the range of data you want to be in the list. This will create a pull down menu when the user activates that cell. P.S. The list of items needs to be in the same sheet. Hopefully this will help you. -- goober ------------------------------------------------------------------------ goober's Profile: http://www.excelforum.com/member.php...o&userid=19838 View this thread: http://www.excelforum.com/showthread...hreadid=480850 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
External data source | Excel Discussion (Misc queries) | |||
UNC for external data source | Excel Discussion (Misc queries) | |||
ListBox - Row Source problem | Excel Programming | |||
ListBox - Row Source problem | Excel Programming | |||
listbox.row source | Excel Programming |