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 299 times.

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