| How to insert a list box on a
and use it to transfer data on to an Excel
worksheet in Microsoft Excel:
Watch the Microsoft Excel training video for a complete demo:
- Create a list of items on sheet2 in the range a2:a9
- Slect 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 listbox, select properties
- In the properties window under the item Name change
the name from ListBox1 to 'lstItems'
- Under the RowSource of the ListBox 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
- 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
- The item name will be transferred to the Excel Sheet
1 Range("C2") or Cell C2