VBA question with drop down lists

[slabs]

Hi,

In the attached spreadsheet I am setting up a way to quote on different products where your choice in one column affects the choices in others.

You first choose dropdown in column D, then E, then F, then G. I have used a cascading dropdown list (dropdown sheet) to affect this, plus some programming.

My problem is that one can mistakenly change the order of entry and then get incorrect values. So would want the program to force a person to always change column D first. Maybe the entry must change to "choose.." if one changes any other column first.

Second problem: when you delete entries a message pops up "Run-time error 1004" Cannot enter null value. What code is needed to change this.

Last issue: In column G when you choose a colour a message should pop up that is in column H of the product table sheet. It, however, only pops up when you come back to the cell and click on it. How can it pop up immediately from entering your choice on the drop down list.

Help will be much appreciated!

[shirleyxls]

To answer just your first question about drop down lists:

In your data validation add an IF statement to check if the next cell in the row is blank and only show the drop down list if it is. I use a "dummy" list (which doesn't actually exist) to do this. The validation will warn that it results in an error, but you can ignore that.

So something like this for the validation in E7:

=IF(F7="",Product_List,INDIRECT("Blocked"))

Give it a try and see what you think.

[shirleyxls]

Ref your other 2 questions:

On the VBA error, you just need to add an IF statement to check that the Target.Address value is not blank before doing your filters. If it's blank, how do you want it to behave? Do you want to remove the filter completely, or leave it as it was before the change, or set it to a default value?

For your message display problem you need to run the message code as part of the Worksheet_Change event I think. Running it under SelectionChange will only run the code when you move to another cell which is why you don't see the message immediately.

I'm a bit pushed for time this morning, but give those a try and I will try to have another look later today.

[shirleyxls]

Was quicker than I thought!

Try this:

Please Login or Register  to view this content.

You'll still need to decide what you want to do about the filters if a cell is blank, but hopefully this will get you started. I commented out the SelectionChange code otherwise you'll get the message twice; you can just delete that sub when you're happy everything is working.


Excel is a constant learning process and it's great to help each other. If any of us have helped you today, a click on the star on the left is appreciated.

[slabs]

Thanks! I still need to get my head around the way to stop people arbitrarily changing choices on the drop down lists.

Will also try using the IF statement in the data validation.

[shirleyxls]

No problem.

If you get stuck, just post another thread or message me through the forum and I'll try to help.