Automatically Update/Populate Listbox based on selections of other listbox

[Shortz]

Hi Guys,
thanks in advance again! You're continued support and coding knowledge is awesome! Can i please get your assistance on the following?

I have a userform with 3 listboxes that the user makes selections in. The user makes selections in order , i.e. listbox 1 to 3. All listboxes are multiselect.
Ideally what i'm looking to try to create is listbox 1 will automatically detect the options selected in it and will populate listbox 2 and so on.

Questions:
1. Is it possible to update listbox 2 automatically once the user has made their selections in one? and listbox 3 based on 1 and 2?

2. Is it possible to achieve this without having to make a button the user must click to populate each listbox? I have experimented with the _Change procedure, but i don't think this is practical as every time there is a change, it triggers codes.

3. Finally, i noticed in the _Change experiment that some of my previous selections from previous listboxes would disappear, is there a way to prevent this from happening?

4. If all of this is too complex, what are my alternatives?

thanks heaps!
Shortz

[mdmackillop]

Have a look at this item
http://vbaexpress.com/forum/showthread.php?t=31511
If you still have queries, please post a sample workbook containing your userform.

[Shortz]

I have a question or two regarding listbox selections. Which does relate to my previous posts.

1. what might causes selections in listboxes to become 'unselected' in a userform that isn't unloaded? (apart from direct references i.e. clicking)
I have an example in the attachment where, in a userform you make two selections under the names and skills categories and then populate the task lists based on the these selections. When the task list macro (code below) runs it somehow deselects the skills and names. Is there a common reason that you guys can think of?

2. Do i need to setup code a certain way to prevent this or do i have to create a 'save' code to temp store the lists and reproduce them?

(Despite what the images suggest i'm actually using excel 2003)

Here is the code that disrupts the selections. You'll notice some of your handywork in here! I really appreciate the help you guys gave to make it.

[vba]Sub TaskList()
'v0.3 filters the tasks based on the selections made in the task list
'not activated if 'all' skills are selected
Dim x%, j%, i%
Dim arr()
Dim ListofTasks As Range

'copies all the skills from the original page 'all' to the hidden sheet
Sheets("All").Activate
Range(Cells(5, 1).End(xlDown), Cells(5, 2)).Copy _
Sheets("Hidden").Cells(2, 5)
Sheets("Hidden").Activate
Range(Cells(1, 5), Cells(1, 6)).Value = "All"

'this is sets the selected skills as an array to be used later
x = SQForm.SL.ListCount - 1
ReDim arr(x)
For i = 1 To x
If SQForm.SL.Selected(i) = True Then
arr(j) = SQForm.SL.List(i)
j = j + 1
End If
Next i
If j = 0 Then
Exit Sub
End If
ReDim Preserve arr(j - 1)
On Error Resume Next

'matches values in the array to Col 5 text, if exists then next,
'otherwise deletes that cell and the one to the right (col6)
x = Sheets("Hidden").Cells(1, 5).End(xlDown).Row
For i = x To 2 Step -1
If Not Application.Match(Cells(i, 5), arr, 0) > 0 Then
Range(Cells(i, 5), Cells(i, 6)).Delete (xlShiftUp)
End If
Next

'once the results have been filtered sets the rowsource of the listbox
'to Col 6 of the hidden sheet
Set ListofTasks = Sheets("Hidden").Range(Cells(2000, 6).End(xlUp), Cells(1, 6))
SQForm.TL.RowSource = "=Hidden" & "!" & ListofTasks.Offset.Address

End Sub[/vba]

[Aussiebear]

Perhaps its just a typo but, this line

[VBA]Range(Cells(1, 5), Cells(1, 6)).Value = "All"[/VBA]

I thought should have been

[VBA]Range(.Cells(1, 5), .Cells(1, 6)).Value = "All"[/VBA]

When ever I see this line of code, I normally expect to see the dots in front of "Cells".

There's another line further down in your code as well.

[Shortz]

Perhaps its just a typo but, this line

[vba]Range(Cells(1, 5), Cells(1, 6)).Value = "All"[/vba]
I thought should have been

[vba]Range(.Cells(1, 5), .Cells(1, 6)).Value = "All"[/vba]
When ever I see this line of code, I normally expect to see the dots in front of "Cells".

There's another line further down in your code as well.

Can you please explain why? what does the dot add to the code? all the other places i've used this type of code have been without a dot. Does the dot change the range reference?

EDIT: I've just tested the dots and it appears that vba doesn't like it, it errors out. When have you used this yourself?