Alternating MsgBox

[lfh123]

Hello

I'm having some trouble trying do make this idea work.
I have a value on a cell that is the result of some calculations and when the result is equal or less then zero an msgbox shows up saying "Zero".
I managed to get this far but the next part is where i'm stuck.
I'm trying to make the msgbox show only once and when the result changes do something greater than zero it shows "Not Zero" but it can only appear if the first message already appeared and if the result becomes zero again it shows he "Zero" message again.

Is it possible do to something like this?

Thanks

[Bob Phillips]

Add a static variable to the procedure, say a boolean, test it before showing the MsgBox, and set it after the first showing.

[lfh123]

Thanks for the quick reply.
But the thing is that i'm not very good at coding and the only thing i manage to do is this:

Private Sub Worksheet_Calculate()
If Range("B29").Value <= "0" Then
    MsgBox "Zero"
Else
    MsgBox "Not Zero"
End If
End Sub

[Bob Phillips]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Static WasZero As Boolean

    If Not Application.Intersect(Target, Me.Range("B29")) Is Nothing Then
    
        If Target.Value <= 0 Then
    
            If Not WasZero Then MsgBox "Zero"
            WasZero = True
        Else
        
            If WasZero Then MsgBox "Not Zero"
            WasZero = False
        End If
    End If
End Sub

[lfh123]

Thanks, that is 99% of what i need, the only thing is that "B29" is the result of an subtraction, i tried to change "Worksheet_Change" to "Worksheet_Calculate" but it didn't work

[Bob Phillips]

You would need to check B29 specifically rather than Target.

[mikerickson]

If the precedent cells are on the same sheet you can use

Private Sub Worksheet_Change(ByVal Target As Range)
    Static ZeroFlag As Boolean
    Dim KeyCells As Range
    
    Set KeyCells = Range("B29")
    On Error Resume Next
    Set KeyCells = Application.Union(KeyCells, KeyCells.Precedents)
    On Error GoTo 0
    
    If Not Application.Intersect(Target, KeyCells) Is Nothing Then
        If (Range("B29").Value <= 0) Xor ZeroFlag Then
            MsgBox IIf(ZeroFlag, "Not zero", "zero")
            ZeroFlag = Not (ZeroFlag)
        End If
    End If
End Sub

[lfh123]

Hello everyone

Thanks to your help is almost working, but there are other calculations that feed the subtraction that feeds the cell.
I've attached the file so you guys can take a look. The tab that is the cell that will trigger the alert is called "DRE".

Thanks.

[lfh123]

It's working
I've altered the first code that i've used and included your suggestion, it ended up like this.

Private Sub Worksheet_Calculate()    Static ZeroFlag As Boolean
        If Range("B29").Value <= "0" Xor ZeroFlag Then
            MsgBox IIf(ZeroFlag, "Lucro Atingido", "Lucro Zero")
            ZeroFlag = Not (ZeroFlag)
    End If
End Sub

Thanks everyone for the help

Please mark this question as closed and if possible remove the file from the thread.

Thanks again