ListBox external source

[DoctorG]

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

[goober]

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.

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[François]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>