Userform with 2 Listboxes

[Guest]

Hello,
I would like to create a Userform for data input and on the Userform I need
two Listboxes. The first would be populated with data from the range "Depts"
and the second would populate to a range based on what is selected in the
first listbox.

I am able to do this on a normal worksheet using the Validation, but I would
like to do this on a Userform.

Is this possible? Thank you in advance.
Mike

 

[Jim Cone]

Mike,

This should help...
http://support.microsoft.com/?kbid=213748
How to Populate One List Box Based on Another List Box

Jim Cone
San Francisco, USA

 

[Guest]

Mike

In addition to Jim's suggestion you might want to try using the INDIRECT
function:

This example might be used for a make believe car dealer letting a customer
select first a car model and then a color for that particular model.

First, I used a list of Cars: Civic, Accord, Spirit etc. and named this
range: Cars
I named another cell: MyCar
I then used the Car models as headers for new Named Ranges: Civic, Accord...
These must be exact duplicates of the items in the Cars list.
Below each of these headers I made a list of available colors for each
model. These will vary per model.
I then created another Name ---Insert>Name>Define ---typed CarColor in the
top box where the name goes. In the RefersTo box I typed: =INDIRECT(MyCar)
--with the equal sign.
I have done this with comboboxes rather that Listboxes but it should be the
same.
Create a UserForm with Combobox1 and Combobox2

In the Properties window I entered MyCar in the RowSource for Combobox1. In
the code for the userform I entered:

Private Sub ComboBox1_Change()
Range("MyCar") = Me.ComboBox1

'Fill the list for the next cbo
Me.ComboBox2.RowSource = "CarColor" 'This is the INDIRECT list
End Sub

Private Sub ComboBox2_Change()
Range("D20") = Me.ComboBox2 'Adjust D20 as needed
End Sub

--------------------------------
An advantage to using this Indirect method discussed in the KB article is
that you don't have to make a long Select Case set of code lines and access
to changing the list is more direct- a range on a worksheet.

Hope this helps