Using a ListBox on an Excel UserForm

Home More Videos

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
  • 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
  • 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:

Watch the video on youtube