Search Contextures Sites

Contextures
Excel news
by email

 

 

 

Learn how to create Excel dashboards.

 

30 Excel Functions in 30 Days

 

 

Learn how to create Excel dashboards.

 

 

 

 

Learn how to create Excel dashboards.

 

 

Time-saving
Pivot Table add-in

 

 

 

 

Learn how to create Excel dashboards.

Excel VBA Move ListBox Items on UserForm

Create a UserForm  
Create the ListBoxes  
Create the Buttons  
Name the Buttons  
ListBox Move Items Code 
ListBox Move Items Sample Workbook

Thanks to Dave Peterson, who contributed this technique to move ListBox items.

For the worksheet version of this example, please visit the Excel VBA Move ListBox Items page.

This Excel VBA example moves items from one listbox to another, on an Excel UserForm -- either all the items, of just the selected items.

Create a UserForm

First, create an Excel UserForm, and then follow the steps below, to add

  • two listboxes,
  • four command buttons to move the items, and a
  • command button to close the form.

Create the ListsBoxes

On the UserForm, you'll add two ListBoxes:

  1. Press the Alt + F11 keys, to open the Visual Basic Editor.
  2. In the Project Explorer window, double-click on the UserForm, to select it. Here, the UserForm is named frmListBoxTest.
  3. In the ToolBox, click the ListBox control
  4. On the UserForm, draw the outline of a ListBox at the left side of the UserForm.
  5. Then, use the ListBox tool again, to draw a ListBox on the right side of the UserForm, or copy and paste the first ListBox.

The ListBoxes will keep their default names -- ListBox1 and ListBox2.

Create the Buttons

Next, follow these steps to add buttons between the ListBoxes.

  1. In the Toolbox, click the Command Button control.
  2. On the UserForm, drag to draw an outline for the first command button, between the two listboxes.
  3. Use the command button control to draw 3 more buttons, or copy and paste the first button.
  4. Below the listboxes, add one more button, that will be used to close the form.

Command Button control

Name the Buttons

The buttons between the listboxes will get the following names:

  • BTN_moveAllRight
  • BTN_MoveSelectedRight
  • BTN_moveAllLeft
  • BTN_MoveSelectedLeft

The buttons below the listboxes will be named cmdOK.

To name a button:

Click on the top button, and in the Properties window, change the button name, and change the Caption. (Use the < and > keys to create arrows)

Change button properties

Then, click on each of the buttons below, and change its name and caption.

Add the UserForm Code

Finally, add the following code to the UserForm's code module. To add the code:

Right-click on the UserForm, and click View Code

View Code

Paste sample code (from the section below) at the top of the UserForm module (delete the Option Explicit line, if you copied it from the sample code)

Paste Code

ListBox Move Items Code

Copy the following code to the worksheet module.

Option Explicit
'Move Listbox Items in UserForm
'code from Dave Peterson
'posted on www.contextures.com


Private Sub BTN_moveAllLeft_Click()

    Dim iCtr As Long

    For iCtr = 0 To Me.ListBox2.ListCount - 1
        Me.ListBox1.AddItem Me.ListBox2.List(iCtr)
    Next iCtr

    Me.ListBox2.Clear
End Sub


Private Sub BTN_moveAllRight_Click()

    Dim iCtr As Long

    For iCtr = 0 To Me.ListBox1.ListCount - 1
        Me.ListBox2.AddItem Me.ListBox1.List(iCtr)
    Next iCtr

    Me.ListBox1.Clear
End Sub


Private Sub BTN_MoveSelectedLeft_Click()

    Dim iCtr As Long

    For iCtr = 0 To Me.ListBox2.ListCount - 1
        If Me.ListBox2.Selected(iCtr) = True Then
            Me.ListBox1.AddItem Me.ListBox2.List(iCtr)
        End If
    Next iCtr

    For iCtr = Me.ListBox2.ListCount - 1 To 0 Step -1
        If Me.ListBox2.Selected(iCtr) = True Then
            Me.ListBox2.RemoveItem iCtr
        End If
    Next iCtr

End Sub


Private Sub BTN_MoveSelectedRight_Click()

    Dim iCtr As Long

    For iCtr = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(iCtr) = True Then
            Me.ListBox2.AddItem Me.ListBox1.List(iCtr)
        End If
    Next iCtr

    For iCtr = Me.ListBox1.ListCount - 1 To 0 Step -1
        If Me.ListBox1.Selected(iCtr) = True Then
            Me.ListBox1.RemoveItem iCtr
        End If
    Next iCtr

End Sub

Private Sub cmdOK_Click()
 Unload Me
End Sub

Private Sub UserForm_Initialize()

    Dim iCtr As Long

    With Me.ListBox1
        For iCtr = 1 To 10
            .AddItem "This is a test" & iCtr
        Next iCtr
    End With

    With Me.ListBox2
        For iCtr = 1 To 10
            .AddItem "This is a not a test" & iCtr
        Next iCtr
    End With

    Me.ListBox1.MultiSelect = fmMultiSelectMulti
    Me.ListBox2.MultiSelect = fmMultiSelectMulti

End Sub

ListBox UserForm Move Items Workbook

To see the code, and test the ListBox move items code for a UserForm, you can download the ListBox UserForm Move Items sample workbook. The file is in Excel 2007 format, and is zipped. It contains macros, so enable them if you want to test the code.

   

Related Excel VBA Tutorials

 

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: February 17, 2014 3:39 PM