Nested / Conditional / Dependent drop down in VBA

vivekp

New Member
Joined
Apr 15, 2014
Messages
1
I've been racking my brain trying to figure how to solve this for the last week and a half. The core of my formula works in Excel but not in VBA.

The basic concept I'm trying to accomplish is to make one drop-down dependent on the result of another drop-down.

The first drop-down has a list of parent categories. I wrote a macro that cycles through all of the parent categories and creates a named range for each parent category that contains the relevant subsidiary categories choices for the second drop-down. I tested this in Excel and it works beautifully with indirect and offset and match but when I tried it in VBA it errors out. I've tried a lot of different iterations but let me show you the latest:

Here is the data from which the drop downs are created (sheet name is "Colors"). The range names column is something that my previous macro already created. The upgrade column contains the drop-down choices for the first drop-down. The columns with color as their title contain the subsidiary drop-down choices. The range name refers to these subsidiary choices.

[TABLE="width: 647"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]Range names[/TD]
[TD]Upgrade[/TD]
[TD]Color 1[/TD]
[TD]Color 2[/TD]
[TD]Color 3[/TD]
[TD]Color 4[/TD]
[TD]Color 5[/TD]
[/TR]
[TR]
[TD]_[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]_StandardCarpet[/TD]
[TD]Standard Carpet[/TD]
[TD]Brown[/TD]
[TD]Green[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]_1stUpgradeCarpet[/TD]
[TD]1st Upgrade Carpet[/TD]
[TD]Black[/TD]
[TD]Brown[/TD]
[TD]Green[/TD]
[TD]Blue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]_2ndUpgradeCarpet[/TD]
[TD]2nd Upgrade Carpet[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]_3rdUpgradeCarpet[/TD]
[TD]3rd Upgrade Carpet[/TD]
[TD]Brown[/TD]
[TD]Green[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]_4thUpgradeCarpet[/TD]
[TD]4th Upgrade Carpet[/TD]
[TD]Yellow[/TD]
[TD]Purple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Here is the code that I'm using:

ActiveCell.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Operator:= xlBetween, Formula1:="=if(isblank(activecell.offset(0,-1))," & Chr(34) & "N/A" & Chr(34) & ",indirect(offset(colors!a1,match(activecell.offset(0,-1),colors!b:b,0)-1,0,1,1) ) )"

I have tried a whole variety of different combinations. I tried hard coding the reference cell, adjusting the error-checking in the beginning of the if statement, changing the alert style. I have not tried many different formulas in terms of indirect, match, offset but I did try that exact text directly in Excel (with of course the appropriate adjustments for non-VBA) and it worked perfectly.

Seeing as how I've spent about 15 to 20 hours on this one line of code, any help would be unbelievably appreciated.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top