Listbox Preselection Based on other Listbox and Range

[jerem]

thread is also posted on
http://www.ozgrid.com/forum/showthread.php?t=157850
and
[URL]http://www.excelforum.com/excel-programming/791467-listbox-preselection-based-on-other-listbox-and-range.html[/URL]

Hi
I have a userform with several listbox. Listbox1 lists Regions (A,B,C) and Listbox2 lists company names. In sheet1 range ("A10:B34"), companies are listed (column A) with corresponding regions (Column B). What I would like to achieve is: If regions are selected in Listbox1, all the companies in those regions are selected in Listbox2.
I came up with the following code but it is not working properly. 2 problems:

1. Only the first selected region in the Listbox1 is working, meaning, if I select region a,b and c. Companies from region a only will be highlighted in Listbox2, while all of them should be highlighted. If I unselect a, then, b companies will be highlighted, while it should be b and c companies.
2. If I only have one region selected in Listbox1 and I deselect it, Listbox2 still shows the companies of that region as selected. It should not.


Thanks for any help

Private Sub ListBox1_Change()
Dim i, j As Integer
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
 
If ListBox1.ListIndex = -1 Then Exit Sub
For i = ListBox1.ListCount - 1 To 0 Step -1
For j = ListBox2.ListCount - 1 To 0 Step -1
If ListBox1.Selected(i) = True Then
If Application.VLookup(ListBox2.List(j), ws.Range("a10:b34"), 2, False) = ListBox1.List(i) Then
ListBox2.Selected(j) = True
Else
ListBox2.Selected(j) = False
End If
End If
Next j
Next i
End Sub

 

[jerem]

Solved in

http://www.ozgrid.com/forum/showthread.php?t=157850&goto=newpost**