ListBox in Excel VBA – Adding, Clearing, Multiple Selection Examples

Home/Excel VBA/ListBox in Excel VBA – Adding, Clearing, Multiple Selection Examples

VBA ListBox 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 VBA ListBox Excel .

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


VBA ListBox Excel – Example Cases:

VBA ListBox 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.

mongopono.ru-ListBox

LIMITED TIME OFFER
By |August 24th, 2013|Excel VBA|21 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

21 Comments

  1. November 30, 2013 at 7:05 AM - Reply

    hi,

    Thanks for your example, very well written.

    I was wodnering if you could simplify the code this way ?

    Private Sub cmdMoveSelLeft_Click()

    ‘Variable Declaration
    Dim iCnt As Integer

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

    End Sub

    Or even more simplified by using one sub with parameters, parameters being the name of the controls. Somthing like this :

    Sub transfert_one_or_several_items(source As String, destination As String)
    Dim i As Integer
    For i = Controls(source).ListCount – 1 To 0 Step -1
    If Controls(source).Selected(i) Then
    Controls(destination).AddItem Controls(source).List(i)
    Controls(source).RemoveItem (i)
    End If
    Next
    End Sub

    Regards

    Gaetan

    • PNRao December 1, 2013 at 9:59 PM - Reply

      Hello Gaetan,
      Thanks for your suggestions. Our focus is providing fundamental concepts on VBA with proper examples to explain the use in real-time (particularly for data analysis applications).
      Yes, we can simplify the codes even further, it will be easy for advanced programs. We will provide more simpler way of writing VBA Programs and best practices in near future.

      Thanks
      PNRao!

  2. Joe December 27, 2013 at 5:55 AM - Reply

    Is there a way to save the selection you make? It seems, if you make a selection and save the workbook it won’t save upon re-opening (e.g. if i choose “south” then save, then I re-open, “south isn’t in Listbox2).

    • PNRao December 28, 2013 at 3:54 AM - Reply

      Hi Joe,
      Excel will not save selected options in the listbox. However, you can save selected items using VBA.

      1. Put the selected option in a Range on BeforeSave Event:
      Example: in your case, we put ‘South’ in Sheet1 at Range A1

      2. Populate the Items in the Range while opening the workbook (On Workbook Open Event)
      Listbox2.value=Sheet1.Range(“A1”)

      Hope this clarifies your doubt.

      Thanks-PNRao!

  3. Mike January 22, 2014 at 6:21 AM - Reply

    Hi!

    this is very helpful for a starter like me..
    Im just wondering on how to code if i want to fill the list box by values which is in another sheet in a column.
    this is for easy maintenance. Not hardcoding all the needed values.

    Thanks,
    Mike

    • PNRao January 22, 2014 at 11:28 PM - Reply

      Hi Mike,

      You can do this in two different ways.

      1. Without using VBA: Place a listbox form control (Not an activeX Control)in your worksheet. Then right click on it and choose the input range to fill the items in the listbox.
      (To insert listbox: Goto Developer Tab in the ribbon => under developer tab click on the Insert in the Controls group under)

      2. Using VBA: Loop through the range using for or do while loop and add the item to an activex listbox:
      place a listbox (activex control) in your worksheet, let’s assue you have placed a listbox (ListBox1) in Sheet1. And you have the the data in Sheet2 for Range A1 to A10. The following procedure will populate the list box, you can call this in the workbook open event.

      Sub populateListbox()
      Sheet1.ListBox1.Clear
      For i = 1 To 10
      Sheet1.ListBox1.AddItem Sheet2.Cells(i, 1)
      Next
      End Sub

      Hope this helps.
      Thanks-PNRao!

  4. Jov February 25, 2014 at 6:46 AM - Reply

    Hi, This is very help like me as newbie. what if i have a userform with 3 textboxes which i want to fill the listbox using textboxes data.

    Thanks

    Jov

    • PNRao February 25, 2014 at 11:20 PM - Reply

      Hi Jov,
      Thanks for your comments.
      You can use same method like:


      ListBox1.Clear
      ListBox1.AddItem TextBox1.Value
      ListBox1.AddItem TextBox2.Value
      ListBox1.AddItem TextBox3.Value

      if you have many textboxes, you can do something like below:

      ListBox1.Clear
      For iCntr=1 to 10
      ListBox1.AddItem Me.Controls("TextBox" &iCntr).Value
      Next

      Hope this helps!
      Thanks
      PNRao

  5. Erin June 23, 2014 at 11:05 PM - Reply

    I followed excatly all of the steps as outlined – and although my items move between for the “all” buttons they do not move for the “select” buttons. Is there something else I can do for the “select” buttons? Thanks.

  6. Alex August 4, 2014 at 10:47 PM - Reply

    Hi,
    I may have two problems with the code you’re providing.
    First with this line:
    Me.ListBox1.RemoveItem iCnt
    There, VBA’s always giving me an error.

    Second with the saving of the values:
    I’m using a range for my listbox values so they can be save. In fact, all the items in my list that are selected are being send to cells and when I reopen, I use ListFillRange to repopulate my ListBox. The problem is that I would like to modify my selection after reopening the file and VBA won’t allow it because the ListBox can’t be clear.

    • PNRao August 17, 2014 at 11:48 AM - Reply

      Hi Alex,

      It should work fine, could you please provide me the example file.

      Thanks-PNRao!

  7. Thadak September 17, 2014 at 2:03 AM - Reply

    Hi,
    Thanks for your code … really helpful in our project.
    I have one question… we are hardcoding the source values in Listbox1 similarly like your code. when we select the values to Listbox2 and again when you move back that value from listbox2 to listbox1 … that value is going to last row of Listbox1. Is there any way to get display in Listbox1 as we hardcoded in the source?

    • Amol March 11, 2016 at 2:43 AM - Reply

      If I have many controls like textbox,combobox & DTpicker then how to pull listbox items in there

      Any idea ?

      Thanks in advanced

  8. Nagarjuna November 17, 2014 at 11:37 PM - Reply

    hi,
    I am new to VBA macro and also VBA code. I have an excel sheet containing following respective columns :-Region Company, SicCode, Address, City, ZIP, ContactName, Telephone, etc………….
    Here i have to perform some data cleaning work like to eliminate exceptions from company names ( -,”,’,, , numbers, etc.,), SicCode exceptions ( should only contain numbers, not alphabets and no special characters), zip code exceptions ( should be in 6 characters and one space between 3 characters like “A1B 2NO”, should not be like “A1B2NO”), Contact name should be in alphabets in proper no leading and trailing spaces, telephone should be in numberic and should not contain special characters (ex: “” ).

    for all these data clean, how can i write the code and execute. pls help me in session.

  9. December 30, 2014 at 12:09 PM - Reply

    Hi,
    May i request you to kindly give codes and examples to change the range of listbox connected to a worksheet depending on the rows of data. i have a worksheet with 25 rows of data, which is a list of distributors. When the option to display the list is selected thru a command button to run the macro, If i added 10 more names, my listbox should display all 35 names. But, if i delete 10 names, from the original, it should display only 15 names. i.e, end range of listbox should be able to change to last row of data. To say, originally A1:A25, if 10 names added, A1:A35, if 5 names deleted then A1:A30 and so on.

  10. March 16, 2015 at 6:21 PM - Reply

    Hi,
    Thanks for infos.
    I made an example of this issue :
    Advanced Filter With Userform .
    View link :

  11. Bitten May 3, 2015 at 6:35 PM - Reply

    Hello,

    thank you Villa for this useful example. However, I noticed that there is something missing in subs called ‘cmdMoveSelLeft_Click()’ and ‘cmdMoveSelRight_Click()’. I mean, when you select the last item on the list (let’s it is listbox1, but the same happens with listbox2), then, while it is moved from listbox1 to listbox2, all the items are automatically removed from listbox1.

    This part of code is responsible for this situation:

    For iCnt = Me.ListBox1.ListCount – 1 To 0 Step -1
    If Me.ListBox1.Selected(iCnt) = True Then
    Me.ListBox1.RemoveItem iCnt

    End If
    Next

    I guess you should add ‘exit for’ at the end of your if-statement, so the code will look like this:

    If Me.ListBox1.Selected(iCnt) = True Then
    Me.ListBox1.RemoveItem iCnt
    Exit For
    End If

    This should solve the problem I described above.

  12. iasmer May 8, 2015 at 12:38 PM - Reply

    Thank You for convenient code. I use part of it for move items from left listbox to right on user form, but slightly optimise:

    ‘=== [ < ]
    'Move Selected Items from Listbox2 to Listbox1
    Private Sub cmb_Move_Left_Click()
    Dim iCnt%, j%
    j = 0
    iCnt = Me.lbx_NewNames.ListCount – 1
    While j ]
    ‘Move Selected Items from Listbox1 to Listbox2
    Private Sub cmb_Move_Right_Click()
    Dim iCnt%, j%
    j = 0
    iCnt = Me.lbx_OldNames.ListCount – 1
    While j <= iCnt
    If Me.lbx_OldNames.Selected(j) = True Then
    Me.lbx_NewNames.AddItem Me.lbx_OldNames.List(j)
    Me.lbx_OldNames.RemoveItem j
    iCnt = iCnt – 1
    End If
    j = j + 1
    Wend
    End Sub

    May be someone find this useful

  13. raghu January 10, 2017 at 1:56 PM - Reply

    multiple selection from listbox1 to listbox2 not working

  14. raghu January 10, 2017 at 4:45 PM - Reply

    How to copy paste columns of the list items selected in listbox???

  15. raghu January 10, 2017 at 4:45 PM - Reply

    How to copy paste columns of the list items selected in list box in worksheet???

Leave A Comment


Related pages


writing vba code in excel 2010v lookup functionlcase vbaexcel 2007 dashboard tutorialcrack excel macro passwordvba excel for eachrun a query in access vbahow to do sumif in excelusing arrays in excelhow to unprotect excel without passwordsum if vbachart layout exceljob tracking excel templateunlock vba projectvba current cellcylinder chart in excelsave excel file vbaexcel apply chart templatecountif formula in excelexcel vba filter rangeexcel worksheet calculatevba excel close fileuserform propertiesmultiple if statements excelif isnumbermicrosoft excel merge cellsinterview questions on sql pdfexcel template project timelinerun a query in exceldefine function vbaexcel if statement stringhr interview questions indiabixactive x definitiontrim function in vbaexcel 2013 dashboard designdelete cells vbacommand buttons in excelms excel tutorial with exampleswhat is vlookup function in excelhow to consolidate two excel spreadsheets into oneaccess vba backcolor codesvbscript message box numbered list and valuesexamples of excel macrosexcel shotcuthow to remove blank lines in excelexcel formulas monthvba excel range variablepivoting in excelhow to merge data from different excel filescreate an excel macroexcel 2013 pivot table wizardisna function excelhow to enter a carriage return in excelvbokcancel vbainput box in vbaexcel formula functions and examplesexcel 2007 unprotect workbookvlookup excel 2007 tutorialunlock excel file password protectedexcel vba last cellhow to apply vlookup formula in excelsql database for beginnersdashboard creation in excelmicrosoft excel test questions and answerspassword protect excel 2010 workbookiferror excel vbaxml to excel converterexcel vba accessexcel hide sheetvba to create pivot tablehow to remove excel formulaarrange numbers in ascending order worksheetshow to find out duplicate records in excelcommon excel macros