An Excel VBA listbox, is a drop down list from where the user can make a choice. Learn how to draw a listbox on your worksheet and how to add items to a listbox.
Excel 2010 and Excel 2007 users. Click on Insert from the Developer tab and then click on List Box in the ActiveX Controls section.
Excel 2003 users. Click on List Box from the Control ToolBox.
1. Draw a listbox on your worksheet.
To add some items to the listbox, execute the following steps.
1. Add the following code lines to the Workbook Open event (or add them to your own code).
.AddItem "New York"
Note: use Sheet2 if your listbox is located on the second worksheet.
2. To link a cell to the listbox, right click on the listbox (make sure design mode is selected) and click on Properties. Fill in D3 for LinkedCell.
Note: also see the ListFillRange property, to fill a listbox with a range of cells.
3. Close Excel and reopen your Excel file.
4. Note: if you use these code lines in your own code, outside the Workbook Open event, add the following code line at the start of your code.
This way your items won't be added multiple times, when you execute your code more than once.
Although in some situations it can be useful to directly place a listbox on your worksheet, a listbox is particularly useful when placed on a Userform.
Did you find this information helpful? Show your appreciation, vote for us.