Create Default for Dependent Drop Down List

**moskealy** · 02-18-2010, 10:44 AM

Hi,
I'm using dependent drop-down lists. In the first box the user can choose a color which drives a set of options in the second box. (e.g. User selects "Red" and in the second dropdown they can then choose "Red A", "Red B", "Red C", etc.). Right now, if you select "Orange", it will still show "Red A" from the previous selection until the user selects the dropdown in the dependent box to refresh the list of options. Is there a way to make it so that second dropdown box will default to "Orange A"?

I tried writing some code to do this, but I can't seem to make it work. I've attached my file.

Thanks!!!!

**NBVC** · 02-18-2010, 10:55 AM

Googling... i found this thread:

http://www.keyongtech.com/5297423-ho...alidation-cell

you can use T. Valko's event macro or Link to Debra Dalgleish's site and use her non-macro workaround.

**moskealy** · 02-18-2010, 03:11 PM

Thanks, but I'm hoping not to blank out the contents. What I'd like to do is the following:

If the dropdowns start as
Color: Red
Type: RedA

When you switch to Color: Orange, it should change Type to the first of the Orange list which is OrangeA. As it stands now, the Type stays the same so you see Color: Red and Type: OrangeA which is an invalid option.

I tried writing code but it doesn't seem to be working right.

**JBeaucaire** · 02-18-2010, 04:14 PM

I've run into this issue. If you download a sample file from here:
Jerry Beaucaire's Excel Tools
The file you want is: --DependentLists3.xls - 3 levels

There's a macro active on the CHOICES sheet (right-click the sheet tab and VIEW CODE) and you'll see it. It watches for changes to earlier parent lists causing the dependent lists to clear the now-invalid choices.

You should be able to adapt that to actually insert a default value.

Here's a thread where I helped someone do the default value thing:
http://www.excelforum.com/excel-gene...ult-value.html

**NBVC** · 02-18-2010, 04:18 PM

This one seems to do it:

Please Login or Register  to view this content.

**moskealy** · 02-18-2010, 04:53 PM

This works great! Thanks! Now the only issue is that when I try to change the dependent drop-down from Red A to Red B, it just keeps going back to Red A.

Originally Posted by NBVC

This one seems to do it:

Please Login or Register  to view this content.

**JBeaucaire** · 02-18-2010, 04:55 PM

Maybe this:

Please Login or Register  to view this content.

**moskealy** · 02-18-2010, 05:02 PM

That's it! Thanks so much!

Originally Posted by JBeaucaire

Maybe this:

Please Login or Register  to view this content.

**KyleW** · 05-14-2010, 03:10 PM

The code used in this thread was a great find. Thanks JBeaucaire. I'm hoping for some additional assistance. I have a dependent drop down list in more than one column and don't know how to add an Or statement or If statement to duplicate what is occuring in the target column. Below is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

If Target.Address = "$B$11" Then
Application.EnableEvents = False
Range("B17").Value = "<Select>"
Application.EnableEvents = True
End If

End Sub

If I also want to duplicate this in cells D11 & D17, F11 & F17, etc., how would I do that?

**NBVC** · 05-14-2010, 03:11 PM

Welcome to the forum,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here