title: Userform Listbox populate and clear depending on selection from another listbox
body: <p>I have 2 listboxes, lbA and lbB. I am populating lbA using SQL script which works fine. Now what i would like to do is when i select an item from lbA, lbB gets populated again from a SQL script. It works when selecting only one item from lbA but selecting two or more or unseleting an item then it either adds repeated items or doesnt delete items from lbB. I am puzzled as to how to approach this. Can anyone please help?</p>
  
  <p>This is the code so far:</p>
  
  <pre><code>Private Sub lbA_Change() 
      Dim Num As Integer 
      Dim lngIndex As Long 
  
      If Me.lbA.ListIndex &lt;&gt; -1 Then 
          For lngIndex = 0 To lbA.ListCount - 1 
              If lbA.Selected(lngIndex) Then 
  
  
                  Dim cnPubs As ADODB.Connection 
                  Set cnPubs = New ADODB.Connection 
                  Dim strConn As String 
                  strConn = "PROVIDER=SQLOLEDB;" 
                  strConn = strConn &amp; "DATA SOURCE=MSSQLSERVER2008;INITIAL CATALOG=MyDB;" 
                  strConn = strConn &amp; " INTEGRATED SECURITY=sspi;" 
                  cnPubs.Open strConn 
                  Dim rsPubs As ADODB.Recordset 
                  Set rsPubs = New ADODB.Recordset 
                  With rsPubs 
                      .ActiveConnection = cnPubs 
                      .Open "My SQl Statement" 
                  End With 
                  If rsPubs.EOF Or rsPubs.BOF Then 
                      Exit Sub 
                  End If 
                  rsPubs.MoveFirst 
                  With Me.lbB 
                      .Clear 
                      Do 
                          .AddItem rsPubs![strName] 
                          rsPubs.MoveNext 
                      Loop Until rsPubs.EOF 
                  End With 
                  rsPubs.Close 
  
              End If 
          Next 
      End If 
  End Sub
  </code></pre>