UserForms & dependent ComboBoxes?

**hit_king51** · 05-10-2010, 08:23 PM

Hi all,

Can anyone help out with making a UserForm that has multiple ComboBoxes which have limited choices for the user based on what the previous drop down choice was?

For example, let's say that column A contains states, column B contains cities and column C contains boroughs/neighborhoods/towns.

I'd like to make it so that once the user selects California from the first drop down box, then only cities in California would appear in the second drop down box. Then once they chose their city, only boroughs/neighborhoods/towns from that city would be selectable in the third drop down box.

I appreciate the help!

**Palmetto** · 05-10-2010, 08:55 PM

Basic example attached. This should give you a good idea of how to proceed.

Sheet1 holds lists for which named ranges were created.
Userform contains two combo boxes.

Code in the form initialize event

Please Login or Register  to view this content.

Code for combo box #1

Please Login or Register  to view this content.

**hit_king51** · 05-11-2010, 12:41 PM

Thank you Palmetto. Here's my new (but I think easy to solve) issue.

How can I use lookups in VB to get data populated into certain cells based on what is selected from a drop down menu?

I am able to get data to print on the worksheet with these:
.Cells(lRow, 2).Value = Me.ComboBox1.Value
.Cells(lRow, 4).Value = Me.ComboBox2.Value

But the following gives me an "Expected: list separator or )" error:

.Cells(lRow, 1).Value = Application.VLOOKUP(Me.ComboBox1.Value,J1:K18,2,FALSE)

The range J1:K18 contains cities in column J and corresponding zip codes in column K.

I'd like to print the corresponding zip code based on the city from Me.ComboBox1.Value.

Am I far off?

**Palmetto** · 05-11-2010, 01:02 PM

The range reference is incorrectly qualified: it specifies only cell adresses without telling VBA that it is a range object.

Please Login or Register  to view this content.

Use (adjusting the sheet name as needed):

Please Login or Register  to view this content.

**hit_king51** · 05-11-2010, 02:33 PM

Hmm, I used this:

(The worksheet with the cities and zip code table is called Sheet1 by the way.)

.Cells(lRow, 1).Value = Application.VLookup(Me.ComboBox1.Value, Sheet1!Range("J1:K18"), 2, False)

And I'm getting the following error:

Run-time error '438':
Object doesn't support this property or method

**hit_king51** · 05-11-2010, 08:51 PM

Just bumping for a response.

**Palmetto** · 05-11-2010, 09:30 PM

Oops! Typo in the code - inadvertently used an exclamation symbol (force of habit from typing sheet references in formula).

See previous post for corrected code using the decimal point.