Excel

Identify Selected Item in a single selection Listbox

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 http://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 

			
 '** The following code goes in a userform **
Option Explicit 
 
Private Sub cmdCancel_Click() 
     'Unload the userform
    Unload Me 
End Sub 
 
Private Sub cmdOkay_Click() 
     'Verify that an item was selected
    If ListBox1.ListIndex = -1 Then 
         'If ListIndex is -1, nothing selected
        MsgBox "Nothing was selected!" 
    Else 
         'If ListIndex not -1 inform user what was selected
        MsgBox "You selected " & ListBox1.Value 
    End If 
     
     'Unload the userform
    Unload Me 
End Sub 
 
Private Sub UserForm_Initialize() 
     'Load the combobox with a variety of household pets
    With Me.ListBox1 
         'Clear the rowsource in case it has been set
        .RowSource = "" 
         'Add the items
        .AddItem ("Cat") 
        .AddItem ("Dog") 
        .AddItem ("Gerbil") 
        .AddItem ("Lizard") 
        .AddItem ("Rat") 
        .AddItem ("Snake") 
        .AddItem ("Turtle") 
    End With 
End Sub 
 
 '** The following code goes in a standard module **
Option Explicit 
 
Sub Launch() 
     'This code will launch the userform
    UserForm1.Show 
End Sub

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


This entry has been viewed 368 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express