Message Box trigger for every 100th number

[Moonbeam111]

I have a range with a number in it. I would like for it to alert me every time it reaches 100 more.

For example, if my number is 263, then trigger a messagebox saying when I've reached 300 or more. But only trigger once.

What can I do to get this to work?

Im using this code currently. Which partly works.

If Range("M2") Mod 100 = 0 Then
msgbox "You have triggered the next 100th number"
End If

But it only triggers if the number is exactly divisible by 100. I would like it so it triggers if it reaches the next 100th number without being restricted to exactly the 100th number. I also cant get a way to make it not trigger again if I go under the 100th number and go back up again. I'd like it to be a one time trigger.

 

[myall_blues]

If you're comparing the value in M2 to itself as you update it then what you want becomes much more difficult because the code needs to somehow:

• know which of the values in M2 was the 'original' value
• store that value for comparison against all subsequent number.

If you simplify it by comparing it to a starting value in another cell, it would still need some way of telling if the trigger had occurred.
Something like this, with the starting value in L2:

Public Function CheckSheet(ByVal sSheetName As String) As Boolean
    '
    ' Check required worksheet exists
    '
    Dim bReturn As Boolean
    Dim i As Integer
    For i = 1 To ThisWorkbook.Sheets.Count
        If ThisWorkbook.Sheets(i).Name = sSheetName Then
            bReturn = True
            Exit For
        End If
    Next i
    CheckSheet = bReturn
End Function
Public Sub AddSheet(sName As String, sVisible As XlSheetVisibility, sBefore As String)
    Dim wksNewSheet As Excel.Worksheet
    Set wksNewSheet = ThisWorkbook.Worksheets.Add(before:=Sheets(sBefore))
    With wksNewSheet
        .Name = sName
        .Visible = sVisible
    End With
End Sub
Private Sub TestRange()
    If Range("M2") >= Range("L2") + 100 Then
        If CheckSheet("MacroData_Hidden") Then
            '
            ' If the hidden sheet exists then the next 100th number has already been triggered - do nothing
            '
        Else
            '
            ' Create the hidden sheet
            '
            Call AddSheet("MacroData_Hidden", xlSheetHidden, "Sheet1")
            MsgBox "You have triggered the next 100th number"
        End If
    End If
End Sub

 

[StephenCrump]

I have a range with a number in it. I would like for it to alert me every time it reaches 100 more.

How is this number changing? If it's changed by the user, or by code (and Application.EnableEvents hasn't been disabled), then perhaps something along these lines:

	A	B
1	MyCell	PreviousMax (store somewhere in workbook)
2	263	200
Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim n As Long
        
    If Not Intersect(Target, Range("MyCell")) Is Nothing Then
        n = 100 * Int(Range("MyCell").Value / 100)
        If n > Range("PreviousMax").Value Then
            MsgBox n & " passed.  Previous max: " & Range("PreviousMax").Value
            Application.EnableEvents = False
            Range("PreviousMax").Value = n
            Application.EnableEvents = True
        End If
    End If

End Sub

 

[Moonbeam111]

Thank you both for the attempts.

How is this number changing? If it's changed by the user, or by code (and Application.EnableEvents hasn't been disabled), then perhaps something along these lines:

Your solution works great when I input directly into range ("M2") however, I have a formula inside range("M2") that sums the cell starting at P15 and every 9 cells below that.. (P15, P24, P33 etc...) so Id prefer if it worked without me having to change M2 directly. Do you have an alternate solution for this scenario?

 

[NoSparks]

You will need to use the Worksheet_Calculate event for this.
Because Excel doesn't know which cells with formula changed you'll need to store the M2 value somewhere for comparison.
Try this, I've used column Z for storing things

In the ThisWorkbook module paste this

Private Sub Workbook_Open()
    Call setTriggerPoint
End Sub

In a standard module paste

Sub setTriggerPoint()
    Dim i As Long
   
    ' establish nextTrigger
    With Sheets("Sheet1")
        For i = 1 To 50
            If i * 100 > .Range("M2") Then Exit For
        Next i
        Application.EnableEvents = False
        ' store next trigger
        .Range("Z4") = i * 100
        ' store previous trigger
        .Range("Z3") = .Range("Z4") - 100
        ' store M2 value
        .Range("Z2").Value = .Range("M2").Value
        Application.EnableEvents = True
    End With
   
End Sub

and in the sheet module paste

Private Sub Worksheet_Calculate()

    With Range("M2")
        If .Value = Range("Z2").Value Then
            Exit Sub
        Else
            If  .Value >= Range("Z4") Then
                MsgBox "You have triggered the next 100th number"
                Call setTriggerPoint
            End If
        End If
    End With
      
End Sub

 

[StephenCrump]

Or, provided any changes to P15, P24, P33 will trigger the Worksheet_Change Sub, simply change Post #3 code:

'From
If Not Intersect(Target, Range("MyCell")) Is Nothing Then
'To
If Not Intersect(Target, Range("P:P")) Is Nothing Then

You can modify the range reference based on the formula in M2, e.g. perhaps it could be limited to Range("P15:P1000")?

 

[Moonbeam111]

That seems to work thank you.

I have another question but its similar so I hope I don't have to make another thread.

How would I modify your code to include a different cell Range("M84").

When m84 is equal to or less than half, trigger the message box but only once?

And also trigger when m84 is less than or equal to 0 but only once?

I tried to modify it myself but I couldn't get the message box to only appear once. I must be doing something wrong.

Appreciate the help.

 

[StephenCrump]

How is M84 changing? User input or formula?

If it's a formula, what cell(s) does it rely on, and are these user input or formula?

 

[Moonbeam111]

My apologies. Its actually M85. But anyway M85 is a formula. Here is what is actually in the cell, if it helps.

=SUMIF(C20:L100,">0")

It simply takes any positive number in the range and adds it up.

However I have two other cells: M84+M106.

M84 = 180
M106 = M84 - M85

What I am trying to accomplish is to make it so that when M106 is less than or equal to half of M84, a message box pops up saying "You have reached half of the max value"
Also, would like it to message me when M106 <= 0 message box "You have reached all of the max value"

 

[StephenCrump]

Similar concept as before, requiring a storage cell called "PreviousUsed", which should be blank initially.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim n As Long
    Dim d As Double
    Dim s As String
   
    If Not Intersect(Target, Range("P:P")) Is Nothing Then
        n = 100 * Int(Range("MyCell").Value / 100)
        If n > Range("PreviousMax").Value Then
            Application.EnableEvents = False
            Range("PreviousMax").Value = n
            Application.EnableEvents = True
            MsgBox n & " passed.  Previous max: " & Range("PreviousMax").Value
        End If
    End If
   
    If Not Intersect(Target, Range("C20:L100")) Is Nothing Then
        d = Range("M85").Value / Range("M84").Value
        s = IIf(d >= 1, "all", "half")
        If (d >= 0.5 And Range("PreviousUsed").Value = "") Or (d >= 1 And Range("PreviousUsed").Value = "half") Then
            Application.EnableEvents = False
            Range("PreviousUsed").Value = s
            Application.EnableEvents = True
            MsgBox "You have reached " & s & " of the maximum value"
        End If
    End If

End Sub