Execute Macro On Calculated Cell change [Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Have tried several approaches, but nothing works

Have a cell that has a formula and changes based on other cells - want to execute a macro when ever the formula causes the cell value to change

Excel 2007

any help is appreciated. . .

________________________________________________________________________

I am new to this forum - I think I put the following comments in the answer section - my apologies. . .

________________________________________________________________________


The cell address that changes (has a formula in it) based on other cells on the data input form is C125

The cell is also protected - but even if I unprotect it, I still can't get it to execute the macro.

Here is the code I have used that I can't get to to wrk -

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$125" Then
Call WorkSheet_Calculate
End If
End Sub

I know this should be quite simple . . . but I am not seeing my mistake.

Thanks

4 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
My bad. You did mention formula update, but I had missed it. '

This will not work as value changed by formula will not trigger it


This will trigger if a value is updated by formula
Private Sub Worksheet_Calculate()


End Sub


How ever looking at your example,I would say the you need to trigger the source of change

Lets say that your C125 formula is A125 + B125

Then you trigger your recalc routine when A125 or b125 changes
I must be not seeing something because

Private Sub Worksheet_Calculate()

End Sub

doesn't work -

The macro Worksheet_Calculate is actually a misnomer - all it does is add 4 more rows for data input. Three of the rows are input the fourth is a sq. ft. calculation that is totalled in C125. . .

I put the above code in the worksheet code area - the declaration says calculate - what am I missing?

Thanks
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
The routine

Private Sub Worksheet_Calculate()

End Sub


Is an event in a sheet. If your was just a regular routine that was named so, bad idea. If you go to your VBE environment and double click on the sheet there. you will find that routine there. Of course it will be blank till you fill it up. But that event will not help you as it seem that you only want to run the macro if certain address is updated. This event is triggered if any formula is recalculated. Basically you have to get innovative it you plan to trigger this macro only for a certain cell
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
you would need to define for the sheet this routine

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub
I mistakenly put this in the wrong area - it was a late night . . . please see my question area

Thanks