Multiselect listboxes

[theduckman16]

Hello -

I would greatly appreciate some help with a problem I have come across.

I have a form that has two listboxes and listbox1 is populated with the column headers in my worksheet. I want to be able to have the user select columns on the left and "add" them to listbox2. Here is the code that I have so far:

Private Sub CommandButton1_Click()
strMsg = "You chose this entry from the list box: " & vbCr
For i = 1 To ListBox1.ListCount
    If ListBox1.Selected(i - 1) = True Then
        strMsg = strMsg & ListBox1.List(i - 1) & vbCr
    End If
Next i
MsgBox strMsg

 

[JP2112]

Something like this perhaps?

Private Sub CommandButton1_Click()
' check if anything is selected
If Me.ListBox1.ListIndex = -1 Then Exit Sub
 
For i = 1 To Me.ListBox1.listCount
  If Me.ListBox1.Selected(i - 1) Then
    Me.ListBox2.AddItem Me.ListBox1.List(i - 1)
  Next
Next i
End Sub

 

[theduckman16]

Something like this perhaps?

Private Sub CommandButton1_Click()
' check if anything is selected
If Me.ListBox1.ListIndex = -1 Then Exit Sub
 
For i = 1 To Me.ListBox1.listCount
  If Me.ListBox1.Selected(i - 1) Then
    Me.ListBox2.AddItem Me.ListBox1.List(i - 1)
  Next
Next i
End Sub

That works perfectly. Now do you know a way to delete all of the columns that are not selected in listbox2?

thx

 

[theduckman16]

Here is my code thus far.

Private Sub UserForm_Initialize()
For i = 1 To ActiveSheet.Columns.Count
     Me.ListBox1.AddItem ActiveSheet.Cells(1, i)
Next i
End Sub
 
Private Sub CommandButton1_Click()
Dim i As Integer
If ListBox1.ListIndex = -1 Then Exit Sub
For i = ListBox1.ListCount - 1 To 0 Step -1
    If ListBox1.Selected(i) = True Then
        ListBox2.AddItem ListBox1.List(i)
        ListBox1.RemoveItem (i)
    End If
Next i
End Sub
 
Private Sub cmdRemoveColumns_Click()
Dim i As Integer
If ListBox2.ListIndex = -1 Then Exit Sub
For i = ListBox2.ListCount - 1 To 0 Step -1
    If ListBox2.Selected(i) = True Then
        ListBox1.AddItem ListBox2.List(i)
        ListBox2.RemoveItem (i)
    End If
Next i
End Sub
 
Private Sub cmdOK_Click()
End Sub

I need to be able to delete all of the columns that are not in listbox2.

 

[theduckman16]

I assume that I have to store the values from listbox2 and have the column.count match up with the values.

 

[theduckman16]

Have I stumped everyone?

 

[Norie]

Do you mean you want to transfer the column headers from one listbox to another?

ie move and delete

Try this lightly checked code.

Option Explicit
 
Private Sub CommandButton1_Click()
Dim I As Long
    For I = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(I) Then
            ListBox2.AddItem ListBox1.List(I)
        End If
        Next I
        
     For I = ListBox1.ListCount - 1 To 0 Step -1
        If ListBox1.Selected(I) Then
            ListBox1.RemoveItem I
        End If
     Next I
     
End Sub
Private Sub UserForm_Initialize()
Dim rng As Range
 
    ' first header on Sheet1
    Set rng = Worksheets("Sheet1").Range("A1")
    
    ' populate ListBox1 with headers from Sheet1
    While rng.Value <> ""
        ListBox1.AddItem rng.Value
        
        Set rng = rng.Offset(, 1)
    Wend

    ListBox1.MultiSelect = fmMultiSelectMulti
    
End Sub

I'm pretty sure there's a way to do it without 2 loops, but can't remember right now what it is.

 

[Norie]

This seems to work, but it also seems a bit convoluted.

Dim I As Long
Dim lCnt As Long
    lCnt = ListBox1.ListCount
    
    Do
    
        If ListBox1.Selected(I) Then
            ListBox2.AddItem ListBox1.List(I)
            ListBox1.RemoveItem I
            lCnt = lCnt - 1
           I = I - 1
        End If
    I = I + 1
    
    Loop Until I >= lCnt

 

[JP2112]

This board has dozens of active threads, staffed by unpaid volunteers. You haven't stumped us, you just haven't waited long enough for a reply.

Are you saying you want to delete the columns from the worksheet that aren't represented in ListBox2?

 

[theduckman16]

This board has dozens of active threads, staffed by unpaid volunteers. You haven't stumped us, you just haven't waited long enough for a reply.

Are you saying you want to delete the columns from the worksheet that aren't represented in ListBox2?

Yes, that is correct.