Execute Macro On Calculated Cell changeClosed

Question

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

Configuration: Windows XP / Internet Explorer 7.0

rizvisa1 · Answer

you would need to define for the sheet this routine

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

rizvisa1 · Answer

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

Notar · Answer

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

rizvisa1 · Answer

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

Execute Macro On Calculated Cell change

4 responses

Similar discussions

Subscribe To Our Newsletter!