Clear value in Data Validation box when another Validation box changes value

[RyanChristy]

Dear all,

I have 2 data validation boxes, and the options in the second box are dependant upon the selection in the first.
I need the second box to clear whenever the first box selection changes, can I do this using VBA without using Controls?

Many Thanks

Ryan

 

[Smitty]

Welcome to the Board!

Sure, a Change event will respond to validation changes. Here's a boilerplate Change event example.

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("xxx")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH

 

[RyanChristy]

Thank you for your reply Smitty,

I'm not new to excel but new to VBA, and I'm struggling to edit the code.
I have 10 pairs of data validation boxes, so 10 A's and 10 B's. Each data validation box has its own range.
The range of the A's are:- B281:I281, B285:I285 etc..
The range of the B's are:- B282:I283, B286:I287 etc..

Just to clarify, the positioning of these validation boxes are as follows:

A1
B1
A2
B2
A3
B3

So what I need is for B1 to clear when A1 is changed, but i wouldnt want any other cells clearing unless their parent A changed.

I hope I have explained this well enough - I really appreciate your help!

Many Thanks

Ryan