How to call a procedure when a cell value changes?

[Guest]

Hi,

I have a formula in cell B49 that calculates based on certain inputs.
How can I trigger a procedure to fire when the value of calculation in B49
changes?

Also, how can I supress a dialog in a macro?

Thanks in advance,

Bojana

 

[Guest]

You have to catch the change of the precednts of the formula. You can not
directly catch the change of the calculated cell. Depending on what the
prcidents of the formula looked like I might use union and intersect to make
things easy something like... (Where the precidents are B1 and B48)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Union(Range("B48"), Range("B1"))) Is Nothing Then
MsgBox "Tada"
End If
End Sub

As for the dialogues it depends what they are but

Application.DisplayAlerts = False
Application.DisplayAlerts = True

will turn alerts on and off if that is what you meant by dialogues.

 

[STEVE BELL]

In the worksheet module:
Say B49 is dependent on A1, B1, C1
=========================================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" or Target.Address = "$B$1" or Target.Address
= "$C$1"
' procedure to run
End If
End Sub
==========================================

There is also the calculate event, but I am not sure how to trap
a given cell change
=========================================
Private Sub Worksheet_Calculate()
' maybe you can find a way to compare the before and after value of B49
' (one way is to make this make put the value of B49 in another cell,
than

If Range("B49") <> Range("AZ49") then
' run procedure

Range("AZ49").Value = Range("B49")
End If
End Sub
=========================================

to turn off Alerts - place this in any code:
========================
Application.DisplayAlerts = False
' your code
Application.DisplayAlerts = True
========================
hth

 

[Guest]

It still does not work, probably because I am doing something wrong. I must
say I know very little of VB. I am not familiar with intersect, union etc. Is
there a place where I could read about it?

I have named all the cells that preceed my target cell (B49). If I read this
code well, if the value of one of the listed cells changes, procedure
Run_solve will be run.

What I am missing in the code below is any reference to my target cell (B49)

Here is what I have now:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Union(Range("C46"), Range("O46"), Range("U46"),
Range("AA46"), Range("AG46"), Range("AM46"), Range("AS46"))) Is Nothing Then
Run_solve
End If
End Sub

What do I need to change to make it work? Could someone please help?

Thanks

 

[STEVE BELL]

I'm not to sharp with Intersect. But it asks the question: Is my range
(Target
included in the following range (the cells referenced by B49)
[check VBE Help to get more info]

But the idea is to use the Worksheet_Change event.
This fires every time any cell on the worksheet is manually edited.
Target is a range name attached to the changed cell.
If Target is equal to any one of the cells that B49 depends on than
do your thing.