How to Run VBA Code on cell or range change
 

You test for your range

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
' do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"MikeZz" wrote in message
...
I want to run some code whenever someone changes a value in a particular
cell
or range. I know how to do it on Worksheet_Change but don't want to have
it
actually run code (slowing response down) every time any cell changes.

Is there a way to do this?

Thanks,
MikeZz

How to Run VBA Code on cell or range change
 

"MikeZz" wrote in message
...
Hi Bob,
I've never used Target/Intersect or even Worksheet_Change before and Excel
Help dialog doesn't always spell things out in layman's terms.


Worksheet_Change is one of the built-in events, which provide some extremely
useful application possibilities. Chip Pearson also noticed the help
deficiencies, and has written a good page on them at
http://www.cpearson.com/excel/events.htm


Can you explain how this line works? "(ByVal Target As Range)"

Is "Target" just a variable which is accepting the values in the range
that
is being changed? How does the routine know to send just the changed
values
or range?


Exactly that. Excel takes care of passing the changed range to the event
code, all you have to do is to test whether that range is part of the range
that you want to test.


and what does "Application.EnableEvents = False" does for me?
Based on your code, I could have an error and would exit the routine
before
EnableEvents is set back to True. Intuitvily this doesn't sound quite
right
but I've never used it so I'm just asking.


Switching EnableEvents off stops the event procedure calling itself over and
over if you change some other cells within the code.

If the code errors, it gets trapped by the line

On Error Goto ws_exit

and goes to that code label, which doesn't just exit, the first thing that
happens after that is to turn events back on, so order is restored.


I've been doing excel programing for years in my job and every time I
think
I've done just about everything, I find an entire new relm of
possibilities.


We're all in that boat <g.

How to Run VBA Code on cell or range change
 

"MikeZz" wrote in message
...

So "Target" is just the range, not the values in the range? By your
comment,
I'd assume that the "Intersect" is not checking the values to the range in
the code, just checking to see if the the changed range is part of the
compared range?

That's correct. Target is a range object, to get the values you have to use
the Value property (and the Cells property if it is a multi-cell range).

Your other assumeoption re Intersect is also correct, and that is how you
stop the code executing for any change, you can control it to execute just
for your specified range, or even take different actions depending upon
which which range the changed cell is part of.