Run a Macro after a cell changed due to a calculation

T

Tornados

I'm sorry but i posted this already in programming. Yet i noticed the
remark that it would be better to post it here. As it isn't the most
simple question around.

Goodday,

I tried to use the worksheet change functions, yet they only seem to
act when you actively enter into a certain cell.

However i need to call a certain macro when the output in a certain
cell changes due to the change in the specific if function. Therefore
without user intervention.

Is there any way to do this?

Kind regards,

Ivo Geijsen
 
D

Dave Peterson

Take a look at the worksheet_calculate event.


I'm sorry but i posted this already in programming. Yet i noticed the
remark that it would be better to post it here. As it isn't the most
simple question around.

Goodday,

I tried to use the worksheet change functions, yet they only seem to
act when you actively enter into a certain cell.

However i need to call a certain macro when the output in a certain
cell changes due to the change in the specific if function. Therefore
without user intervention.

Is there any way to do this?

Kind regards,

Ivo Geijsen
Click to expand...
 
T

Tornados

I"m sorry but i'm not sure how i could use this in this case.. when
exactly does that event show up.. isn't that some constant loop that
will also post an event when nothing is changed?
 
D

Dave Peterson

Each time the worksheet is calculated, this event fires:

Option Explicit
Private Sub Worksheet_Calculate()

Static OldVal As Variant

If IsEmpty(OldVal) Then
OldVal = Range("a1").Value
End If

If Me.Range("a1").Value = OldVal Then
'nothing changed
Else
MsgBox "your code here"
OldVal = Me.Range("a1").Value
End If

End Sub

But you have a few more responses to your other post.
 
T

Tornados

Thanks. I will have a look.

I don't think that i have other replies though at the other post..

Cheers
 
T

Tornados

Dave, Tx! it works for a single cell..

Just to tease your mind one last time...when i try this to do for a
range a1:b3 for example, it says run time error 13.. type mismatch.

In other words, without copy pasting a lot :) , is it possible to do
this for a bigger range?

Tx
 
D

Dave Peterson

In a private reply:

Option Explicit
Private Sub Worksheet_Calculate()

Static myVals As Variant
Dim iRow As Long
Dim iCol As Long
Dim myRng As Range
Dim SomethingChanged As Boolean

Set myRng = Me.Range("a1:b2")

If IsEmpty(myVals) Then
myVals = myRng.Value
Else
SomethingChanged = False
For iRow = LBound(myVals, 1) To UBound(myVals, 1)
For iCol = LBound(myVals, 2) To UBound(myVals, 2)
If myVals(iRow, iCol) = myRng.Cells(iRow, iCol) Then
'do nothing--no change here
Else
SomethingChanged = True
MsgBox "cell: " & myRng.Cells(iRow, iCol).Address(0, 0) _
& " changed"
End If
Next iCol
Next iRow
If SomethingChanged Then
myVals = myRng.Value
End If
End If

End Sub

And adjust your range accordingly. (Your private email was different than the
newsgroup post--but I misread both and used A1:B2!)
 
Top