One time Message Box Pop Up

[Smashley]

Hi!

I have a group of cells on sheetA which contain SUMFIS formulas connected to sheetB. Amounts are posted on sheetB and then the sums are calculated on sheetA.

I have conditional formats set on the sums on sheetA to go red once the sums exceed a maximum amount. However, I also want to add a ONE TIME message box to show up once the maximum sum is surpassed. I have been able to use an If statement within VBA to show once the max amount is exceeded, but then it just keeps popping up.

I'm new to VBA, so I'm not sure how to correct.

My code for one specific cell is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Me.Range("E18")
If Target.Value > 50 Then
MsgBox "blah blah blah"
End If
End Sub

Any expert advice will be much appreciated!

 

[BuSchuetz]

I'm no expert, but I would use a flag cell to indicate if the message box has already been displayed. Something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Me.Range("E18")
If Target.Value > 50 AND Me.Range("ShownFlag") = 0 Then
MsgBox "blah blah blah"
Me.Range("ShownFlag") = 1
End If

'Reset the flag if the value falls below threshhold
If Target.Value <= 50 Then
Me.Range("ShownFlag")= 1
End If
End Sub

where "Shownflag" is a single cell named range somwhere on the sheet.

Maybe the experts here have a more elegant solution ...

 

[BuSchuetz]

Sorry, typo:
OF course the flag must be set to "0" to reset it

I'm no expert, but I would use a flag cell to indicate if the message box has already been displayed. Something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Me.Range("E18")
If Target.Value > 50 AND Me.Range("ShownFlag") = 0 Then
MsgBox "blah blah blah"
Me.Range("ShownFlag") = 1
End If

'Reset the flag if the value falls below threshhold
If Target.Value <= 50 Then
Me.Range("ShownFlag")= 0
End If
End Sub

where "Shownflag" is a single cell named range somwhere on the sheet.

Maybe the experts here have a more elegant solution ...

 

[Smashley]

Sorry, typo:
OF course the flag must be set to "0" to reset it

Thanks for the input!

Unfortunately, it appears that it changes the value in that cell to '0' after it runs and completely erases the existing SUMIFS formula.

There may be no simple way to pull this off.

 

[StephenCrump]

'Code Module
Public bTargetReached As Boolean
Public MyTarget As Double

'ThisWorkbook Module
Private Sub Workbook_Open()
    
    MyTarget = 50
    bTargetReached = Worksheets("Sheet1").Range("E18").Value > MyTarget

End Sub

'Sheet1 Module
Private Sub Worksheet_Calculate()
        
    If Range("E18").Value > MyTarget And Not bTargetReached Then
        MsgBox "Sum has passed " & MyTarget & " for the first time"
        bTargetReached = True
    End If
        
End Sub

Or, if you want the message every subsequent time the cell value goes below and comes back above 50:

'Code Module
Public MySum As Double
Public MyTarget As Double

'ThisWorkbook Module
Private Sub Workbook_Open()
    
    MyTarget = 50
    MySum = Worksheets("Sheet1").Range("E18").Value

End Sub

'Sheet1 Module
Private Sub Worksheet_Calculate()
    
    If Range("E18").Value > MyTarget And MySum <= MyTarget Then MsgBox "Sum has just passed " & MyTarget
    MySum = Range("E18").Value
    
End Sub