Trigger a macro when result changes

[abesimpson]

I have a requirement for VBA code which will moniter several ranges of cells for changes. When any of the cells changes (<> EMPTY) I need to execute a macro. This can happen several times before the spreadsheet closes.

Key requirements:
1 - I need to start monitioring for changes AFTER I "arm" the spreadsheet. I do this by running a macro which fills the target range.

2 - The macro needs monitor a range of cells not just one cell

This article "Trigger a macro when a formula result changes (http://vbaexpress.com/kb/getarticle.php?kb_id=530)" (sorry, too new a Forum member to be able to post a link yet) has code which almost meets my requirements with exceptions mentioned above.

Any suggestions??????

Thanks from a noobie

[mdmackillop]

Are youb able to post a sample workbook? Manage Attachments in the Go Advanced section

[abesimpson]

That is the one thing I can't do as the sheet is not my property. I'll try to be more clear.

A range of cells (Y1:Y5, Y7:Y10, Y.:Y.., Y..:...) in a sheet named "input" is filled with a formula when I manually run an "arming" macro. From this point on I need to moniter the state of any of these cells so that when any of them empty it triggers another macro.

Finally this process may be repeated many times in a day; untill all the cels in the monitered ranges are empty, or the spreadsheet is closed and the process repeated when the spreadsheet is reopened.

Many Thanks

a

[mdmackillop]

This may be a bit simplistic.

[abesimpson]

Simplistic or not I think this will do it provided I can get the following to work

Private Sub Worksheet_Calculate()
Dim rng As Range, cel As Range
Set rng = Union(Range("A1:A5"), Range("A7:A12"))
For Each cel In rng
If cel <> Empty Then change1
End If
End Sub

Where change1 is the name of the macro to be activated, any suggestions?

Thanks again.

a

[mdmackillop]

The code as written is designed to monitor cells containing formulae. As such, the cell will be Empty only if the formula is deleted. Can you expand on what is being changed?

[abesimpson]

Each cell in the target range has a formula when "armed". After the cell is triggered it is empty. I would like to trigger the "change1" macro when any cell is emptied.

a

[mdmackillop]

Triggered is not a term I understand. Can you post the "triggering" code?

[abesimpson]

I am using the term "triggered" as the code is executed from another macro. The key here I think is that the state of the cells changes from having a formula to being empty.

Thanks

[mdmackillop]

If the macro will clear a cell, it is simpler to call Change1 from that routine.

eg

If i = 3 then
cells(5,5).clearcontents
call Change1
else
'Do stuff
End If

[Aussiebear]

What about the following code

Private Sub Worksheet_Calculate()
Dim rng As Range, cel As Range
Set rng = Union(Range("A1:A5"), Range("A7:A12"))
For Each cel In rng
If cel = "" Then
Call Change_1
End If
End Sub

[abesimpson]

AussieBears solution is perfect for my needs.

Thanks to all for the help and guidance.


a

[Aussiebear]

Let me see....

Aussiebear 1 XLD 6,3425,238

Look out Bob I'm on my way!:devil2:

[Bob Phillips]

I didn't join in on this thread Ted, otherwise you would still be on 0 :devil2:

[Aussiebear]

Ah yes that's certainly true...... but then I've devised a method of delaying your ability to see the posts.... :devil2::devil2::devil2:

[mdmackillop]

AussieBears solution is perfect for my needs.

No stopping you now Ted.
:thumb

[abesimpson]

Aussiebear,

Glad to be able to increase your "numbers" by 100%!

a

[Aussiebear]

ROFL.... Now can I go back to sleep, since you are all finished making fun of my pathetic efforts?