How to Transfer Items from Combobox to Listbox

How to Transfer Items from Combobox to Listbox




A combobox collects items and displays as a list under the drop-down list. Normally the combobox contains a list of items from table and can be selected once at the time from the drop-down list. However, the items can also be manually added into the combobox. A list box is a collection of items like combobox, but displays as a list one item per line in the rectangle box. In this How To, I will demonstrate how to copy or transfer the items from combobox to the listbox step by step below:

Combobox with One Column

Step 1. Add ComboBox, Listbox, and Transfer button
• Add Combobox and name it as cboTableList
• Add Listbox and name it as List1
• Add button and name it as cmdTransfer
 
Step 2. Add Items to Combobox
For this example, I want to display a list of all tables from my current program on the combobox Table List. When this form is loaded the list of all tables will be loaded into the combobox. I will not explain in this How To. See more details at: How to Get a Table Name into Combobox.
 
Step 3. Add VBA under the On Click Event Procedure of Transfer button
• Double click the Transfer button to open a Property Sheet
• Select Event tab of Property Sheet
• Select Event Procedure under the On Click
• Click on three dots (…) next to Event Procedure
Add VBA code below under the On Click Event Procedure of Transfer button.
Per code below, we use For Loop to get all items of table names from the combobox cboTableList. We add the item to List1 listbox on each loop. Program will automatically transfer the value of the first column of combobox to the listbox. The Table list combobox has only one column for table name, so it will add the table name to the listbox with a line of code: Me.List1.AddItem (.ItemData(i)).
Private Sub cmdTransfer_Click()
'transfer all items from combobox to listbox
Dim i As Long
  With Me.cboTableList
    For i = 0 To .ListCount - 1
       Me.List1.AddItem (.ItemData(i))
    Next i
End With
End Sub
Step 4. Click Transfer button
After you click on Transfer button, all table names will be copied from Table List combobox to Listbox. In this example, I have only four tables and they are copied to the listbox below.
 




Combobox with Many Columns

For example below, I have a Customer Name combobox with three (3) columns (Customer_ID, CustomerName, and Address). The Customer_ID column is hidden due to the column width is set to zero (0) as shown under the Property Sheet of Customer Name combobox below.
 
There are three columns as displayed under the row source of Customer Name combobox.
VBA code under the Transfer button
In this example, we need to transfer only the customer names from combobox to listbox. The customer name is located in the second column of combobox (Customer_ID, CustomerName, and Address). The index of combobox starts from 0 for column 1, 1 for column 2, and so on. So the index of customer name column 2 is 1. The VBA code is almost the same with the VBA of combobox with one column above. There is only line different is: Me.List1.AddItem (.Column(1, i)).
Private Sub cmdTransfer_Click()
'transfer all items from column 2 of combobox to listbox
Dim i As Long
  With Me.Combo9
    For i = 0 To .ListCount - 1
       Me.List1.AddItem (.Column(1, i))
    Next i
End With
End Sub
After you click on Transfer button, all customer names will be copied from Customer Name combobox to Listbox as shown below.
 

Transfer Item that is selected from Combobox

In this example, we don’t use the Transfer button. However, we want to transfer the item that we select from the combobox to the list box. We will put the VBA code under the After Update Event Procedure of combobox Customer Name.
VBA Code:
Private Sub Combo9_AfterUpdate()
Me.List1.AddItem (Me.Combo9.Column(1))
End Sub
Result After Select Customer John Vang




Related posts