Allow code to run once then not until it's trigger value appears again

Hello

I have the below code in a worksheet

Private Sub Worksheet_Calculate()
If [m5] = 40 Then
Call print_PAB30
End If
End Sub

It works as required, but too well. While ever M5=40 it runs printPAB30 constantly, as it should.

What can I do to the code to make it run once, when M5=40, then wait till m5 value changes, before looking for 40 in m5 again and running printPAB30 again when M5 reaches 40.

Cheers

Move your code to the WorkSheet_Change event. Then you can check which cell has changed and its current value...

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$M$5" And Target.Value = 40 Then
        Call Procedure
    End If

End Sub

Thanks cytop. Your code errors, I think because there are other calculations via formula going on on the sheet.

My code works, I just need a way to have it work once, then "go to sleep", unitl M5 = 40 again

I think it's because what the procedure that is called does... but I can't see that code.

When A5=40, the code below is called, which activates another sheet to print it.

Sheets("bulk").Select
Sheets("bulk").Activate
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
MsgBox "You have just printed the completed PAB30 form"

Maybe I should change the code that returns 40, to the "bulk" sheet, and put your code in that sheet instead of the one I have it now.

Thanks for your persistence.