• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

clear dependent data validation cells

R

Rodrigues

Member
Hi All
I'm using data validation with 2 dependent lists with a possibility to use more.
I would like to know if it's possible to clear the two dependent list when a user decides to change the main cell option(s) on the other two cells.
So, shall we say cell B1 is the main cell and B2 and B3 are the dependent lists, what I would like achieve is that, every time an option from the list is selected on B1, delete cells B2 and B3 if cells are already filled in.
Thanks
R
 
You'll always get a better result by providing a sample - the discipline of creating the problem often leads to insight for you, and it prevents the reader from having to make too many assumptions ...
 
Here is the sample, apologies forgot to add the sample.
Let's say that, after fill in with details on columns A ; B and C, decided to go back and change the selection on one of the columns:
If change selection on column A, clear information on columns B & C
If change selection on column B clear information on column C.
Cheers.
 

Attachments

  • Book1.xlsx
    14.2 KB · Views: 7
Here's a crack at it - it has to be VBA - and we use the on Worksheet Change Event Procedure.

See if this is what you need .. if you've never used VBA there may be some settings you'll need to amend ...
 

Attachments

  • Rodrigues VBA.xlsm
    19.7 KB · Views: 6
Hi David
Thank you very much for your help.
I'm wondering if it is possible to make this work down a couple hundred cells?

Also, could we add to the current VBA that, clear C cells when we just change B's?
Thanks again.
 
Of course - it will work over whatever you tell it.
Have you looked at the code? It is very straightforward - I think you'll quickly see what is happening there, and be able to edit it to your requirements. I'll be happy to help, but I'd be overjoyed if you decide to have a go to change it, as you will be learning new tricks!
Do you know how to locate the code?
 
David Evans said:
Of course - it will work over whatever you tell it.
Have you looked at the code? It is very straightforward - I think you'll quickly see what is happening there, and be able to edit it to your requirements. I'll be happy to help, but I'd be overjoyed if you decide to have a go to change it, as you will be learning new tricks!
Do you know how to locate the code?
Click to expand...
David
please see attached, had a play with code, but unfortunately can't make it work.
After change the code, when change any of A cells clears the one above and I can't figure it out how clear C cells when B is changed. Could you please help?
 

Attachments

  • Rodrigues VBA - Copy.xlsm
    18.8 KB · Views: 7
Sorry I was out of touch all day today on a new project.
I tweaked your code - I think you'll want to change only the row in which the Col A value is changed, for example, if Cell A2 is changed, B2:c2 is cleared. Likewise if B2 is changed, C2 is cleared. You can just keep adding to the If statements for each row you have in your worksheet - it's not elegant, but it will work, otherwise if you want to get a little cleverer, you can create an array of the range, loop through each row value and test them ....
Good try with the code - you were just trying to clear a whole range of cells B2:C20 based on a change in cell A2 - not quite what you intended, I suspect - but that's how we learn - keep playing with it! VBA is quite addictive once you start to get it to work for you!
 

Attachments

  • Rodrigues VBA - Copy.xlsm
    18.9 KB · Views: 13
Hi, I'm Jamal
can somebody help me regarding my enquiry as my attach file.
Sorry that I don't know how to descript, hope that you can understand it.
 

Attachments

  • exp_jamal.xlsx
    45.1 KB · Views: 9
Abdul Jamal said:
Hi, I'm Jamal
can somebody help me regarding my enquiry as my attach file.
Sorry that I don't know how to descript, hope that you can understand it.
Click to expand...

Check out @jeffreyweir post above and follow the links - I * think* that's what you're looking for.

It's always best to start a new Question, rather than piggyback an existing one, BTW ...
 
You must log in or register to reply here.
Back
Top