Excel

Identify the selected item in a combobox

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 combobox has been selected, and identify which item was chosen. 

Discussion:

When using a combo box 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 combo box 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 Me.ComboBox1.BoundValue = vbNullString Then 
        MsgBox "You did not choose an item!", vbOKOnly 
        Exit Sub 
    Else 
        MsgBox "You choose " & Me.ComboBox1.BoundValue, vbOKOnly 
    End If 
    Unload Me 
End Sub 
 
Private Sub UserForm_Initialize() 
     'Load the combobox with a variety of household pets
    With Me.ComboBox1 
        .AddItem ("Cat") 
        .AddItem ("Dog") 
        .AddItem ("Gerbil") 
        .AddItem ("Rat") 
        .AddItem ("Snake") 
        .AddItem ("Turtle") 
        .AddItem ("Lizard") 
    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 combobox 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:

ComboBox.zip 8.02KB 

Approved by mdmackillop


This entry has been viewed 318 times.

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