PDA

View Full Version : Alternating MsgBox



lfh123
03-15-2016, 02:58 AM
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
03-15-2016, 03:12 AM
Add a static variable to the procedure, say a boolean, test it before showing the MsgBox, and set it after the first showing.

lfh123
03-15-2016, 04:24 AM
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
03-15-2016, 08:05 AM
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
03-15-2016, 09:15 AM
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
03-15-2016, 03:35 PM
You would need to check B29 specifically rather than Target.

mikerickson
03-15-2016, 06:16 PM
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
03-16-2016, 04:04 AM
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
03-17-2016, 07:00 AM
It's working :bow::beerchug:
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