Excel Copy Columns With Userform

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.

---

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
End Sub