Move Items Between List Boxes

In this tutorial, we have prepared a template where we can copy columns from a sheet to other sheet using userform. We have a workbook consisting of Database and Report sheets.

We placed 2 list boxes on the userform. Column headers of Database sheet that contained data are listed on Listbox1 with the following VBA codes in UserForm_Initialize procedure :

Dim sutn, lst_column As Integer
lst_column = Sheets("Database").Cells(1, Columns.Count).End(xlToLeft).Column
For sutn = 1 To lst_column                 
 ListBox1.AddItem Sheets("Database").Cells(1, sutn).Value
    If Sheets("Database").Columns(sutn).Hidden = True Then
 ListBox1.Selected(sutn - 1) = True
    End If
Next

lst_column = Sheets("Database").Cells(1,Columns.Count).End(xlToLeft).Column On Database sheet, we learn the last used column number with these codes and assign it to lst_column variable.  In Database sheet, this number is 12 and it indicates column L.

We need to move columns (column headers) that we wanted to copy, from ListBox1 to ListBox2. In the userform, we used the buttons to move items between List boxes.

The codes of button (CommandButton5) that we pressed to move an item from Listbox1 to Listbox2 are as follows :

Private Sub CommandButton5_Click()
Dim deger As String, m As Integer
If ListBox1.ListIndex = -1 Then   'If there is no item selected on listbox,no move will be made.
MsgBox "Choose an listbox item from left", , ""
Exit Sub
End If

deger = ListBox1.Value
For m = 0 To ListBox2.ListCount - 1
    If deger = CStr(ListBox2.List(m)) Then
        MsgBox "This item already exists in ListBox2", vbCritical, ""
    Exit Sub
    End If
Next
ListBox2.ListIndex = -1
 ListBox2.AddItem ListBox1.Value
ListBox1.RemoveItem (ListBox1.ListIndex)
Call animation_to_right
End Sub

deger = ListBox1.Value
For m = 0 To ListBox2.ListCount - 1
If deger = CStr(ListBox2.List(m)) Then
MsgBox "This item already exists in ListBox2", vbCritical, ""
Exit Sub
End If
Next With this loop, it is checked whether the ListBox1 item that we want to move is in ListBox2. If there is already this item in ListBox2, the item isn’t moved. When moving an item from ListBox2 to ListBox1, it is controlled with VBA loop in the same way.

Also, items can be moved from Listbox2 to Listbox1. The selected item in Listbox2 can be moved up or down by pressing the spin button.

We added the following codes to the spin button to move items up and down :

Private Sub SpinButton1_SpinDown()
With ListBox2
If .ListIndex = -1 Then Exit Sub
    If .ListIndex < .ListCount - 1 Then
         .AddItem .List(.ListIndex), .ListIndex + 2
            .RemoveItem .ListIndex
            .ListIndex = .ListIndex + 1
    End If
End With
End Sub

Private Sub SpinButton1_SpinUp()
With ListBox2
If .ListIndex = -1 Then Exit Sub
    If .ListIndex > 0 Then
        .AddItem .List(.ListIndex), .ListIndex - 1
        If .ListCount - 1 = .ListIndex Then
            .RemoveItem .ListIndex
            .ListIndex = .ListIndex - 1
        Else
            .RemoveItem .ListIndex
            .ListIndex = .ListIndex - 2
        End If
    End If
End With
End Sub

We added the codes that trigger the MouseMove event for the 3 buttons on the userform. The image is similar to CSS hover effect.

MouseMove procedure for top button (CommandButton5) :
Private Sub CommandButton5_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
CommandButton5.Width = 38
CommandButton5.Left = 150
End Sub
We have added the MouseMove procedure for the userform so that the button returns to its default location and width when the mouse leaves the button:
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
CommandButton5.Width = 29
CommandButton5.Left = 150

After column headers of Database sheet are sorted on ListBox2, the listed columns in ListBox2 are copied to Report sheet by clicking the Filter button. Report sheet cells are cleared before copying.

VBA Codes of Filter button :

For basliklar = 0 To ListBox2.ListCount - 1
baslangic_satiri = 2
Sheets("Report").Cells(baslangic_satiri - 1, basliklar + 1) = ListBox2.List(basliklar, 0)

Sheets("Database").Range(FirstCell, LastCell).AdvancedFilter _
    Action:=xlFilterCopy, CriteriaRange:=Sheets("Database").Range(FirstCell, LastCell), _
    CopyToRange:=Sheets("Report").Cells(baslangic_satiri - 1, basliklar + 1), _
    Unique:=False
Next
Sheets("Report").Columns.EntireColumn.AutoFit   'Widths of columns are set.

lst_column = Sheets("Report").Cells(1, Columns.Count).End(xlToLeft).Column
For s = 1 To lst_column         'Background color of column headers
Sheets("Report").Cells(1, s).Interior.Color = RGB(218, 238, 243)
Sheets("Report").Cells(1, s).Font.Bold = True
Next

The animated progress bar is showed before columns copying to Report sheet. Progress bar is run on other userforms.

📥 Download sample workbook