run a sub only when one cell changes
 

I know i can run private subs based on something like this

Private Sub Worksheet_Change(ByVal Target As Range)

if target.address = "c20" then
.........

But in the name of efficiency is there a way to name a sub so that it is not
even activated unless a specific cell is changed.

Thanks

run a sub only when one cell changes
 

NDBC,

But in the name of efficiency is there a way to name a sub so that it is
not even activated unless a specific cell is changed.

I do not think so.


Is cell C20 being changed manually or by a formula?

If it is being changed manually, then see below "*Manually:"*.


Is cell C20 being change by a formula? If so, then we would have to
use the Worksheet_Calculate Event and a memory variable.

Please post your workbook - scroll down and see "Manage Attachments".



MANUALLY:
Right click the sheet tab you want the code in, and click on View Code.
Paste the below code there (on the right pane).


Code:
--------------------


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C20")) Is Nothing Then Exit Sub
If Target.Count 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False


'The rest of your code goes here


Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


--------------------




Have a great day,
Stan


--
stanleydgromjr
------------------------------------------------------------------------
stanleydgromjr's Profile: http://www.thecodecage.com/forumz/member.php?userid=503
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119573

run a sub only when one cell changes
 

Is cell C20 being change by a formula? If so, then we would have
to use the Worksheet_Calculate Event and a memory variable.

That is not necessarily true. Consider this Change event code to monitor if
the value from the formula in C20 has changed...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range
For Each C In Target
If Not Intersect(C.Dependents, Range("C20")) Is Nothing Then
MsgBox "C20 just changed!"
Exit For
End If
Next
End Sub

To test it, put some numbers in, say, A1:A10 and some more numbers in
B1:B10, then put this formula in C20...

=SUM(A1:A10,C1:C10)

Now, change some values in the range A1:A10 or C1:C10

--
Rick (MVP - Excel)