ListBox VBA Excel Example Macros Codes for Adding new Items,Moving all Items, selected Items from ListBox to another ListBox,clearing,Multi selection. VBA ListBox in Excel is one of finest control in the Excel. You can show the list of items in the listbox and user can select any item and do different operations. In this tutorial, we will explain different example on using ListBox in VBA Excel .

ListBox VBA Excel – Example Cases:

ListBox VBA Excel -Sample ListBox Design:

  1. GoTo Developer Tab from Menu
  2. GoTo Insert from Controls Part
  3. Insert two ListBox’s and four Buttons from ActiveX Controls
  4. Select 1st Button and then Right Click Goto Properties and click on it
  5. Rename 1st button Name as “cmdMoveAllRight” and enter Caption as “>>”
  6. Select 2nd Button and Rename it as “cmdMoveSelRight” and enter Caption as “>”
  7. Select 3rd Button and Rename it as “cmdMoveSelLeft” and enter Caption as “<"
  8. Select 4th Button and Rename it as “cmdMoveAllLeft” and enter Caption as “<<"
  9. The final design should be as shown below
Screen Shot:

listbox in excel vba- Design

Load Items to ListBox while opening Workbook

You can load the items in the ListBox while opening the Excel Workbook. The following example will show you how to populate the items while opening excel file.

Code:
Private Sub Workbook_Open()
       
    'Add Items to ListBox1 in Sheet1 while opening workbook
    With Sheet1.ListBox1
        .AddItem "East"
        .AddItem "West"
        .AddItem "South"
        .AddItem "North"
    End With
    
End Sub

Output:

Here is the screen-shot of the ListBox with items.
listbox in excel vba -AddItems

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. From Project Explorer Double Click on ThisWorkbook
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook and Close it
  6. Open the file to see the output
  7. You should the see the Items in ListBox1
Move all Items from ListBox1 to ListBox2

You can move the items from one ListBox to another ListBox. It is helpful while developing tools. You can give provide the users to select items and add into another ListBox.

Code:
Private Sub cmdMoveAllRight_Click()
    
    'Variable Declaration
    Dim iCnt As Integer
    
    'Move Items from ListBox1 to ListBox2
    For iCnt = 0 To Me.ListBox1.ListCount - 1
        Me.ListBox2.AddItem Me.ListBox1.List(iCnt)
    Next iCnt
    
    'Clear ListBox1 After moving Items from ListBox1 to ListBox2
    Me.ListBox1.Clear
    
End Sub
Output:

Here is the screen-shot to show you moving the items from one ListBox to another.
listbox in excel vba-Move Listbox1 Items to ListBox2

Instructions:
  1. Please follow the above mentioned design steps
  2. Double Click on the 1st button or Right Click on 1st button and then click on View Code
  3. Copy the above code and Paste in the code window
  4. Goto Sheet1 and then Click on “>>” button
  5. Now all ListBox1 Items are available in ListBox2
  6. Save the file as macro enabled workbook
Move all Items from ListBox2 to ListBox1

The following example will show you how to move all items in on-shot from one ListBox to another ListBox.

Code:
Private Sub cmdMoveAllLeft_Click()
    
    'Variable Declaration
    Dim iCnt As Integer
           
    'Move Items from ListBox1 to ListBox2
    For iCnt = 0 To Me.ListBox2.ListCount - 1
        Me.ListBox1.AddItem Me.ListBox2.List(iCnt)
    Next iCnt
    
    'Clear ListBox1 After moving Items from ListBox1 to ListBox2
    Me.ListBox2.Clear
    
End Sub
Output:

Here is the sample screen-shot.
listbox in excel vba-Move Listbox2 Items to ListBox1

Instructions:
  1. Please follow the above mentioned design steps
  2. Double Click on the 4th button or Right Click on 4th button and then click on View Code
  3. Copy the above code and Paste in the code window
  4. Goto Sheet1 and then Click on “<<" button
  5. Now all ListBox2 Items are available in ListBox1
  6. Save the file as macro enabled workbook
Move selected Items from ListBox1 to ListBox2

You can move only selected items form One ListBox to another ListBox. See the below example…

Code:
Private Sub cmdMoveSelLeft_Click()
    
    'Variable Declaration
    Dim iCnt As Integer
        
    'Move Selected Items from Listbox1 to Listbox2
    For iCnt = 0 To Me.ListBox2.ListCount - 1
        If Me.ListBox2.Selected(iCnt) = True Then
            Me.ListBox1.AddItem Me.ListBox2.List(iCnt)
        End If
    Next
    
    For iCnt = Me.ListBox2.ListCount - 1 To 0 Step -1
        If Me.ListBox2.Selected(iCnt) = True Then
            Me.ListBox2.RemoveItem iCnt
        End If
    Next
    
End Sub
Output:

Here is the sample screen-shot.
listbox in excel vba-Move Selected Items from Listbox1 to ListBox2-Example

Instructions:
  1. Please follow the above mentioned design steps
  2. Double Click on the 2nd button or Right Click on 2nd button and then click on View Code
  3. Copy the above code and Paste in the code window
  4. Goto Sheet1 and selct items from ListBox1, Which we want to move to ListBox2
  5. Click on “>” button
  6. Now ListBox1 selected Items are available in ListBox2
  7. Save the file as macro enabled workbook
Move selected Items from ListBox2 to ListBox1

You can move only selected items form One ListBox to another ListBox. See the below example…

Code:
Private Sub cmdMoveSelRight_Click()
    
    'Variable Declaration
    Dim iCnt As Integer
        
    'Move Selected Items from Listbox1 to Listbox2
    For iCnt = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(iCnt) = True Then
            Me.ListBox2.AddItem Me.ListBox1.List(iCnt)
        End If
    Next
    
    For iCnt = Me.ListBox1.ListCount - 1 To 0 Step -1
        If Me.ListBox1.Selected(iCnt) = True Then
            Me.ListBox1.RemoveItem iCnt
        End If
    Next
    
End Sub
Output:

listbox in excel vba-Move Selected Items from Listbox2 to ListBox2-Example

Instructions:
  1. Please follow the above mentioned design steps
  2. Double Click on the 3rd button or Right Click on 3rd button and then click on View Code
  3. Copy the above code and Paste in the code window
  4. Goto Sheet1 and selct items from ListBox2, Which we want to move to ListBox1
  5. Click on “<" button
  6. Now ListBox2 selected Items are available in ListBox1
  7. Save the file as macro enabled workbook
Clear ListBox Items

You can clear the ListBox using Clear method. The following procedure will show how to clear the ListBox items, this procedure will clear ListBox items before loading an items to ListBox.

Code:
Sub Clear_ListBox()
    
    'Clear ListBox Items
    With Sheet1
        .ListBox1.Clear
        .ListBox2.Clear
    End With

End Sub
Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. We can call this procedure to clear ListBox items before loading items to ListBox
  6. It will clear items from ListBox
  7. Save the file as macro enabled workbook
Select Multiple Items from ListBox

The following example will show selecting multiple items.

Code:
Sub SelectMultipleItems_ListBix()

    'To Select Multiple Items in ListBox
    With Sheet1
        .ListBox1.MultiSelect = fmMultiSelectMulti
        .ListBox2.MultiSelect = fmMultiSelectMulti
    End With
End Sub
Output:

listbox in excel vba-Multiple Item Selection

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. You can call above procedure to select multiple items from ListBox
  6. You should see output as shown above
  7. Save the file as macro enabled workbook
Example File

Download the example file and Explore it.

Excel VBA ListBox Examples

Excel VBA ListBox Properties:

  • BackColor: To sets the back color of ListBox control.
  • BorderStyle: To sets the border style of ListBox control.
  • Height: To sets the height of the ListBox control.
  • Left: To sets the left position of the ListBox control.
  • MultiSelect: To select single or multiple list Items of ListBox Control.
  • TextAlign: To Align the ListBox list items.
  • Top: To sets the top position of the ListBox Control.
  • Width: To sets the width of the ListBox control.

Premium Project Management Templates

120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE80% OFF
BROWSE ALL TEMPLATES

Advanced Project Planning Templates

Excel Templates

VIEW DETAILS

120+ Project Management Templates Pack

Excel | PowerPoint | Word

VIEW DETAILS

ULTIMATE RESOURCE MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

50+ Essential Project Management Templates

Excel | PowerPoint | Word

VIEW DETAILS

Project Portfolio Management Templates

Excel | PowerPoint Templates

VIEW DETAILS

50+ Excel Project Management Templates

Excel Templates

VIEW DETAILS

By Published On: August 24th, 2013Categories: Excel VBATags:

Share This Story, Choose Your Platform!