need VBA to clear dependent data validation lists in several rows
 

I'm on xl '03.

I am creating an order form that utilizes several dependent data validation
lists (aligned in a row) to narrow down what product at what price, etc. I
have 15 rows, so the user could order up to 15 products.

They begin by selecting an item in column D. and the rest goes from there.
Obviously, if they change the item in Column D, the other columns do not
automatically clear. I need the active row to clear itself each time the user
changes the item.

I can accomplish this with a recorder macro or worksheet_change(ByVal Target
As Rage) for one row, but I can not figure out how to get this same principle
to apply to all 15 rows.

IN ADDITION:
I need to allow the user the option to select "Misc" in column D and type in
their own product information and pricing (which will erase my formulas in
those columns).

If they change their mind and want to select another item in column d, the
dependent validation lists and vlookup formulas need to be reinstated
somehow.

Is that even possible?

need VBA to clear dependent data validation lists in several rows
 

Post an example of what you are talking about, as it's confusing....
No one will help
you if they don't understand what you are talking about...

RompStar

On May 9, 2:02*pm, PerplexedPanda
wrote:
I'm on xl '03.

I am creating an order form that utilizes several dependent data validation
lists (aligned in a row) to narrow down what product at what price, etc. I
have 15 rows, so the user could order up to 15 products.

They begin by selecting an item in column D. and the rest goes from there.
Obviously, if they change the item in Column D, the other columns do not
automatically clear. I need the active row to clear itself each time the user
changes the item.

I can accomplish this with a recorder macro or worksheet_change(ByVal Target
As Rage) for one row, but I can not figure out how to get this same principle
to apply to all 15 rows.

IN ADDITION:
I need to allow the user the option to select "Misc" in column D and type in
their own product information and pricing (which will erase my formulas in
those columns).

If they change their mind and want to select another item in column d, the
dependent validation lists and vlookup formulas need to be reinstated
somehow.

Is that even possible?

Me again: Answered part of my own question.
 

Okay, I figured out how to get each row to clear when a new item was selected.
So now I have a sub worksheet_change etc. with several of these (one for
each row)

If Target.Address = "$D$2" Then
Call ItemSelect
End If

BUT I still have the Misc issue.

When a user selects "Misc" from the list in column D, I would like them to
be able to override the dependent validation lists in the following columns
to enter their own information about the misc product.

This means they will be deleting vlookup formulas in some columns as well.

If/when the user changes their "misc" selection back to an item in the
database, I need to be able to automatically restore all the lists and
vlookup formulas.

need VBA to clear dependent data validation lists in several rows
 

Take a look at http://www.contextures.com/xlDataVal13.html

Dan

Me again: Answered part of my own question.
 

I've done something like that

You could have textboxes that activate or become visible when they
select misc .

Then in your vlookup formula you could have an If to accommodate the
textbox value.

Dan