Multiple MsgBox on Calculate

[Kaykay5]

Hello,

I am trying to create a code that when values in cells "g10:g13" goes below a specific number, a msg box pops up. I used the below.
Private Sub Worksheet_Calculate()
If Range("g10").Value < 352 Then
MsgBox "T2 Low Stop Approaching! "
End If
If Range("g11").Value < 362 Then
MsgBox "T3 Low Stop Approaching!"
End If
If Range("g12").Value < 1038 Then
MsgBox "T4 Low Stop Approaching!"
End If
If Range("g13").Value < 1037 Then
MsgBox "T5 Low Stop Approaching!"
End If
End Sub

It does pop up when the value is below, but it does it for every cell. For instance when the value in G10 recalculates to 320, msg box (t2) pops up, but if the value in g11 is calculated and it gets to 312, while g10 is still 320, both msg boxes (T2 and T3) pops up. To get only T3, the value of G10 needs to be above the 352 number. If again cell b19 gets recalculated, the msg boxes also pop up.

I need the messages to pop only when g10:g13 is recalculated and their values go below the relevant limit.

 

[Joe4]

Welcome to the Board!

The problem with "Worksheet_Calculate" is you cannot tell what cell was re-calculated.
It is automatically triggered when ANY cell on the sheet is re-calculated, and it cannot tell which cell was re-calculated (only that some cell was re-calculated).

What exactly are the formulas in cell G10:G13?
If they are calculated from other cells, you may be able to use a "Worksheet_Change" event procedure on the cells that make up those formulas, depending on how those cells are updated (if they are being updated manually, you should be able to do this).

 

[Kaykay5]

Welcome to the Board!

The problem with "Worksheet_Calculate" is you cannot tell what cell was re-calculated.
It is automatically triggered when ANY cell on the sheet is re-calculated, and it cannot tell which cell was re-calculated (only that some cell was re-calculated).

What exactly are the formulas in cell G10:G13?
If they are calculated from other cells, you may be able to use a "Worksheet_Change" event procedure on the cells that make up those formulas, depending on how those cells are updated (if they are being updated manually, you should be able to do this).

Thank you Joe4.

Formulas in G10:G13 are lookups based on hidden sheet (lets call it report a). If values in b10:f13 are changed, g10:g13 looks up the relative value (recorded in report a) based on the new changes.

I found the code online, as I'm not versed in VBA. Would I just change calculate to change and leave the other coding the same?

 

[Joe4]

I found the code online, as I'm not versed in VBA. Would I just change calculate to change and leave the other coding the same?

No, that would not work.

How are the items in B10:B13 being updated?
Are they being updated manually by someone?

If cells B10:B13, then this code should do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   See if update made in cells B10:B13
    Set rng = Intersect(Target, Range("B10:B13"))
    
    If rng Is Nothing Then Exit Sub
    
'   Loop through cells in B10:B13 that were just updated
    For Each cell In rng
'       See which row was updated
        Select Case cell.Row
'           Check G10 if cell 10 is updated
            Case 10
                If Range("G10") < 352 Then MsgBox "T2 Low Stop Approaching!"
'           Check G11 if cell 11 is updated
            Case 11
                If Range("G11") < 362 Then MsgBox "T3 Low Stop Approaching!"
'           Check G12 if cell 12 is updated
            Case 12
                If Range("G12") < 1038 Then MsgBox "T4 Low Stop Approaching!"
'           Check G13 if cell 13 is updated
            Case 13
                If Range("G13") < 1037 Then MsgBox "T5 Low Stop Approaching!"
        End Select
    Next cell
    
End Sub

 

[mikerickson]

This sounds like DataValidation error messages would be useful. Put a DV formula like =($G$10>=352) on every precedent cell for G10 with the Info style warning message of your choice.

 

[Kaykay5]

No, that would not work.

How are the items in B10:B13 being updated?
Are they being updated manually by someone?

If cells B10:B13, then this code should do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
   
'   See if update made in cells B10:B13
    Set rng = Intersect(Target, Range("B10:B13"))
   
    If rng Is Nothing Then Exit Sub
   
'   Loop through cells in B10:B13 that were just updated
    For Each cell In rng
'       See which row was updated
        Select Case cell.Row
'           Check G10 if cell 10 is updated
            Case 10
                If Range("G10") < 352 Then MsgBox "T2 Low Stop Approaching!"
'           Check G11 if cell 11 is updated
            Case 11
                If Range("G11") < 362 Then MsgBox "T3 Low Stop Approaching!"
'           Check G12 if cell 12 is updated
            Case 12
                If Range("G12") < 1038 Then MsgBox "T4 Low Stop Approaching!"
'           Check G13 if cell 13 is updated
            Case 13
                If Range("G13") < 1037 Then MsgBox "T5 Low Stop Approaching!"
        End Select
    Next cell
   
End Sub

Joe4, I truly appreciate your assistance!

This worked perfectly.

 

[Kaykay5]

This sounds like DataValidation error messages would be useful. Put a DV formula like =($G$10>=352) on every precedent cell for G10 with the Info style warning message of your choice.

Mikerickson,

That was my first go to, but it kept failing and I couldn't understand why. In reading through, I'm realizing it is because I initially used it on the cells itself (G10:G13). Thank you so much for your help!
Much easier than running code, but at least if i need the code for future it's there.

 

[Joe4]

You are welcome.
Glad we could help!