Excel 2007 : Reset Drop Down to Basic Value

**uditmathur** · 07-25-2011, 03:24 PM

Hi,

I have made this excel where there is a drop down list created using data validation.

What I want is that whenever I change the employee name in cell "B2" all the values of the drop down cells in cells from "B6 to B10" should change to "NO" whether they have NO or Yes as their value.

I want this change while the excel is open and even on multiple occasions while the excel is open.

Can anybody please help me on this.

The file is attached for reference.

**TMS** · 07-25-2011, 04:35 PM

You can do this with a Worksheet Change event monitoring cell B2:

Please Login or Register  to view this content.

Regards

**uditmathur** · 07-26-2011, 03:40 PM

Originally Posted by TMShucks

You can do this with a Worksheet Change event monitoring cell B2:

Please Login or Register  to view this content.

Regards

Thank you so much TM Shucks.

If you can do me a favour can you please explain what the "if intersect" code does / how does it work?
I am pretty new to vb in excel and am learning through internet so am not good at it.

Thanks for the solution, works perfectly man. I am so happy.

**JBeaucaire** · 07-26-2011, 04:24 PM

Any cell on a sheet can be changed, and any change will "trigger" the Worksheet_Change event macro.

The Intersect() method is used to see if the cell changed happens to "intersect" with the cells you want to trigger the activities of the ws_change macro. If the intersect exists, it will continue, if the cell(s) changed happen to occur outside the watched cells, the macro will abort, not do anything.

EDIT: Additional comments removed, this is a good solution.

**TMS** · 07-26-2011, 04:40 PM

@JBeaucaire: thanks for providing the explanation.

@uditmathur: you're welcome. Thanks for the rep.

Also note that Application.EnableEvents = False/True is used because you are making (more) changes to the sheet within the Change event. This prevents an unnecessary loop through the event handler.

If this has answered your question, please mark your thread as Solved. See my signature for details or the FAQ.

Regards