Search Contextures Sites

	Excel VBA ComboBox Match Required Create a ComboBox   Single Column ComboBox   Multiple Column ComboBox   Prevent Invalid Entries in the ComboBox   Add "Select" Item to ComboBox lists  Download a Sample Excel VBA ComboBox Match file   Watch the Excel VBA ComboBox video   Create a ComboBox This Excel VBA example is based on the instructions for Creating an Excel UserForm With ComboBoxes. On that page, the lists are added to the comboboxes by looping through a named range. In this example, there are a couple of improvements to the UserForm: a simpler technique is used to create the lists. the comboboxes are modified so that only items that match a list item can be entered. We will be working with the two comboboxes shown below -- a single column combo box for Location, and a two-column ComboBox for Parts, showing the Part ID, and the part name.. Single Column ComboBox The Location combobox is named cboLocation. It should show all the locations from a named range -- LocationList -- on the LookupLists worksheet. To set the combobox to use LocationList as its source: In the Visual Basic Editor (VBE), select the UserForm, and click on the Location combobox, to select it. In the Properties window, find the RowSource property, and enter LocationList as the source. Multiple Column ComboBox Next, we'll add items to the Part ComboBox, which using the PartsLookup named range. The Part combobox will have 2 columns -- Part ID and Part description To set the Part combobox to use PartsLookup as its source: In the Visual Basic Editor (VBE), select the UserForm, and click on the Part combobox, to select it. In the Properties window, find the RowSource property, and enter PartsLookup as the source. Prevent Invalid Entries in the ComboBox To prevent invalid entries in the comboboxes, change the ComboBox properties, so a match is required. Select the Part combobox, and in the Properties window, change the MatchRequired setting to True. Repeat this setting change for the Location combobox. Add "Select" Item to ComboBox lists Setting the MatchRequired property to True works well, unless your Excel VBA code tries to clear out the comboboxes, to prepare for a new entry. In this example, when you click the Add This Part button, the code removes the entries from the Part combobox and the Location combobox. When that happens, an error message appears, warning "Invalid Property Value." To prevent that error, add a "Select" item at the top of each named range. Then, in the code, add "Select" to the combobox, after the Add Part button is clicked, instead of leaving the comboboxes blank. Now, the first item in the combobox drop down list is Select, and only items from the list can be entered. Watch the Excel VBA ComboBox Match video
	Download a Sample Excel VBA ComboBox Match file Download the zipped sample Excel UserForm ComboBox Match file to see the completed UserForm and Excel VBA code. Watch the Excel VBA ComboBox Match video

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2012 All rights reserved.	Debra Dalgleish

Last updated: June 11, 2011 8:02 PM