Change Event when leaving a single cell

[shippj]

I want a macro to run automatically (it changes several cells' validation lists on the basis of the text in the active cell) anytime the active cell is exited and the value in the cell has changed. (I use a validataion list to fill the active cell)

I have tried several versions of if statements using a Change Event. My problem is that the change event runs for every change and I just want it to activate when I move from the specific cell. I assume I will have to test for it with an If statement, but how?? It is probably a simple code but beyond my knowledge. So far my tests run when the cell is selected and not when I move from it.

Thanks,
John Shipp

 

[Smitty]

Here's some boilerplate change code that allows you to specify a single cell:


<font face=Tahoma><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</SPAN><br>            <SPAN style="color:#007F00">'   Do your thing here</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[shippj]

Smitty

This works perfectly. I do not understand the code but it does exactly what I wanted.

Thanks,
John

 

[Smitty]

I do not understand the code but it does exactly what I wanted.

By defining rng and then comparing it to the Target location with the Intersect argument you're telling the VBE to exit the sub if the Target and rng don't match. So if you change cell B1, but you've set the rng to be A1, then when the code fires it sees that the target cell is not A1 and it exits.

Make sense?

 

[shippj]

Thanks Smitty for the explanation. It makes sense but I was not aware of the intersect command. I will have a lot of uses for this simple routine. I have a spreadsheet where when you select an item from a validation list, I want it to run and change several other validation lists on the page on the basis of the first selection. This works fine as I have it copy the correct list every time the first validation cell changes.

Thanks again. I would not have found this myself.

john

 

[Smitty]

You might be interested in Aladin Akyurek's explanation of Dependent Lists.