• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro won't run despite cell value change

Lazarous

New Member
Hi Guys,
I have the following code which triggers a Macro if the value of cell A59 is greater than 1. However it only works if I select cell A59 and press enter. Yes I have the Calculations options set to automiatic. The formula does update automatically in cell A59, it just doesn't trigger the Macro until as I said above I select the cell and press enter to refresh formula.

Help please.


Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If IsNumeric(Target) And Target.Address = "$A$59" Then
        Select Case Target.Value
        Case Is > 1: InsertRowsOnValue
        End Select
    End If
    Application.Calculation = xlCalculationAutomatic
End Sub
 

Marc L

Excel Ninja
Hi,​
as the calculation must be set to automatic before … See also the VBA method Calculate.​
And obviously to detect any 'change' by formulas all code must be in the Calculate Event rather than Change.​
 

Lazarous

New Member
Hi,
with the following code I get this notification.

76692


Code:
Private Sub Worksheet_Calculate(ByVal Target As Excel.Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If IsNumeric(Target) And Target.Address = "$A$59" Then
        Select Case Target.Value
        Case Is > 1: InsertRowsOnValue
        End Select
    End If
    Application.Calculation = xlCalculationAutomatic
End Sub
 

Debaser

Well-Known Member
The calculate event does not provide a Target argument. You simply need to check the value of A59. If you only want to run the code when the formula result actually changes, you will need to store the old value in a static or module-level variable (or use the range's ID property). You could also use the Change event but monitor the formula's input cells for changes.
 

Lazarous

New Member
Hi Debaser,
I have tried to monitor one of the formulas input cells but it didn't work either.

Any chance the solution could be added to the file?

I have no idea how to do either store the old value in a static or variable module. I would assume that a variable module would be the go as the reference cell result would be dynamic.
 

Debaser

Well-Known Member
It looks to me like all you need is to check the current value of A59, so you can just use the Calculate event:

Code:
Private Sub Worksheet_Calculate()
on error goto clean_up
application.enableevents =false
If Range("A59").Value > 1 then InsertRowsOnValue

clean_up:
application.enableevents =true
End Sub
 
Top