Multi Colum Listbox in UserForm

[Guest]

Hello all you clever people!

Been having some success with listboxes to collect data on Userforms, but
what I would like to try and do now is have a multi colum listbox, ie I have
the main selection of a "Business Unit" which depending on the choice made
will offer up a list of "Business Area", again depending on the choice made
here then a list of "Departments" to be shown. Is it possible to do this,
can anyone point me in the right direction?

 

[Bob Phillips]

That's not a multi-column listbox, but a multi, dependent listboxes.

Best way is to have an initial Business Unit list on a worksheet and bind
Listbox1 to that, and have a list for all options, named appropriately, for
example

BUs
Sales
Finance
HR
etc.

and then have lists for each, such as

Sales
Corporate
Retail
Internal
etc,

and use a named range, say bu_Sales for this. Create the other lists as
well, then in the code, when a selection is made in Listbox1

Listbox2.RowSource = Range("bu_" & Listbox1.Value).Address(,,,True)

which will load Listbox2 with the appropriate data.

Do the same for the Business Areas.


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks Bob you are a star! That is exactly what I needed, and it all seems
so simple now!
--
Zani
(if I have posted here, I really am stuck!)

That's not a multi-column listbox, but a multi, dependent listboxes.

Best way is to have an initial Business Unit list on a worksheet and bind
Listbox1 to that, and have a list for all options, named appropriately, for
example

BUs
Sales
Finance
HR
etc.

and then have lists for each, such as

Sales
Corporate
Retail
Internal
etc,

and use a named range, say bu_Sales for this. Create the other lists as
well, then in the code, when a selection is made in Listbox1

Listbox2.RowSource = Range("bu_" & Listbox1.Value).Address(,,,True)

which will load Listbox2 with the appropriate data.

Do the same for the Business Areas.


--

HTH

RP
(remove nothere from the email address if mailing direct)