Excel VBA select ListBox item

Ease of Use

Easy

Version tested with

2003

Submitted by:

Ken Puls

Description:

This is very simple example of how to verify that an item in a userform listbox has been selected, and identify which item was chosen. This code is specifically designed for a single selection listbox. If you need code to allow multiple selections, please see KB Entry //www.vbaexpress.com/kb/getarticle.php?kb_id=369

Discussion:

When using a listbox on a userform, it is often important to ensure that your user has picked an item, and then identify what the chosen item was. This is a very simple illustration of how to do both. The listbox in this example is filled via code, but it can easily be filled from a worksheet range as well, using a loop.

Code:

instructions for use

How to use:

1. Open the VBE [press Alt+F11].
2. If the project explorer is not open, press CTRL + R to open it.
3. Navigate to the desired file, right click it and choose "Insert UserForm".
4. Double click the userform in the project explorer.
5. If the "Toolbox" toolbar is not open, open it from the View menu.
6. Add a listbox and two commandbuttons to the userform.
7. Right click one of the commandbuttons and choose "Properties".
8. In the window that pops up, change the [Name] property to cmdOkay, and the Caption variable to Okay.
9. Using the same method, change the second button's [name] to cmdCancel, and its Caption to Cancel.
10. Copy the userform subroutines from above [everything up to the line that denotes the Standard module code].
11. Right click the UserForm in the project explorer and choose "View Code".
12. Paste the code in the right pane.
13. Copy the last procedure from above [Sub Launch].
14. In the project explorer, right click the project name again and choose Insert -> Module.
15. Paste the code in the right pane.
16. Press Alt + Q to close the VBE.
17. Save workbook before any other changes.

Test the code:

1. From the main Excel interface, press Alt + F8 to open the macro dialog box.
2. Choose Launch and click Run.
3. Try clicking okay without anything selected, and then try after selecting an item.

Sample File:

ListBoxSingleSelect.zip 10.05KB

Approved by mdmackillop