Kicking off macro if data in cell changes

Is there a way to kick off a macro if the value of a specific cell changes?
The tricky part is that this particular cell's is driven by a formula (in
other words, the user does not manually enter data in this cell).

Try pasting this into the code for the worksheet in question:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A1").Precedents, Target) Is Nothing Then
MsgBox "Something Preceding Range A1 was changed"
End If
End Sub

Charles

dumb and frustrated wrote:
> Is there a way to kick off a macro if the value of a specific cell changes?
> The tricky part is that this particular cell's is driven by a formula (in
> other words, the user does not manually enter data in this cell).

The calculate event. You would have to use a static variable to record the
value of the cell each time, then compare it to the current value, take
action if appropriate, store the current value over the old value.

--
Regards,
Tom Ogilvy


"dumb and frustrated" wrote:

> Is there a way to kick off a macro if the value of a specific cell changes?
> The tricky part is that this particular cell's is driven by a formula (in
> other words, the user does not manually enter data in this cell).

If you don't mind, could you write me a small example? I understand your
idea...but I'm having trouble creating it. (I'm 4 months old in VBA).


"Tom Ogilvy" wrote:

> The calculate event. You would have to use a static variable to record the
> value of the cell each time, then compare it to the current value, take
> action if appropriate, store the current value over the old value.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "dumb and frustrated" wrote:
>
> > Is there a way to kick off a macro if the value of a specific cell changes?
> > The tricky part is that this particular cell's is driven by a formula (in
> > other words, the user does not manually enter data in this cell).

right click on the sheet tab and select view code.

In the left dropdown at the top of the resulting module, select Worksheet
and in the right dropdown select calculate.

You will get a declaration like this in the module

Private Sub Worksheet_Calculate()

End Sub

add you code there

-----------------------------------------
Private Sub Worksheet_Calculate()
Static oldval as Variant

On Error goto ErrHandler

if not isempty(oldval) then
if Range("B9").Value <> oldval then
Application.EnableEvents = False
mymacro
end if
end if
oldval = Range("B9").Value

ErrHandler:
Application.EnableEvents = True
End Sub
------------------------------------

mymacro would be the name of the subroutine that contains the code you want
to run. It should be in a general module (insert=>module in the VBE)

--
Regards,
Tom Ogilvy

"dumb and frustrated" wrote:

> If you don't mind, could you write me a small example? I understand your
> idea...but I'm having trouble creating it. (I'm 4 months old in VBA).
>
>
> "Tom Ogilvy" wrote:
>
> > The calculate event. You would have to use a static variable to record the
> > value of the cell each time, then compare it to the current value, take
> > action if appropriate, store the current value over the old value.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "dumb and frustrated" wrote:
> >
> > > Is there a way to kick off a macro if the value of a specific cell changes?
> > > The tricky part is that this particular cell's is driven by a formula (in
> > > other words, the user does not manually enter data in this cell).