Using a ListBox on an Excel UserForm

How to insert a list box on a user-form and use it to transfer data on to an Excel worksheet in Microsoft Excel:

Create a list of items on sheet2 in the range a2:a9 for example
Select the range and name it using the name box. We gave it the name ‘items’
Click on the Developer Tab
Select Visual Basic
In the Visual Basic Editor window click on Insert
Select UserForm
On the new form insert two controls – a listbox and a command button using the controls toolbox
Right-click the list-box, select properties
In the properties window under the item Name change the name from ListBox1 to ‘lstItems’
Under the RowSource of the List Box property window enter ‘items’, the name of the range you defined earlier and press enter
The list of items appears in the listbox
Right-click the command button, select properties
In the property window change the name of the command button to ‘cmdTransfer’
Under ‘Caption’ property give the command button a new caption called ‘Transfer Data’
Now double-click on the command button and enter the code between the two lines of code displayed by the Visual Basic editor
Range(“C2”).Value=lstItems.Value
Now go back to your Excel workbook Sheet1
Write Item in cell ‘C1’
Now go back to the Visual Basic editor and click on the run button to start the UserForm
Next select an item from the ListBox and click the command button
The item name will be transferred to the Excel Sheet 1 Range(“C2”) or Cell C2

Watch the Microsoft Excel training video for a complete demo:


Leave a Reply

Your email address will not be published. Required fields are marked *