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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.