Run a Macro after a cell changed due to a calculation

  • Thread starter Thread starter Tornados
  • Start date Start date
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
 
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...
 
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?
 
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.
 
Thanks. I will have a look.

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

Cheers
 
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
 
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!)
 
Back
Top