Message Box trigger for every 100th number

Moonbeam111

Board Regular
Joined
Sep 24, 2018
Messages
96
Office Version
  1. 365
  2. 2010
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.

VBA Code:
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.
 
It doesn't work quite right yet. Its supposed to be if d<=0.5 then the "half" should trigger but if d = 0 then the "all" should trigger. Sorry for the confusion
 
Upvote 0
I don't think there's any confusion. Did you try the code?

My "d" is measuring M85/M84, so if it goes >=0.5, then M106/M84 (which is 1- M85/M84) will dip <= 0.5, which will trigger the "half" message.
 
Last edited:
Upvote 0
Ok I freshly tested your code and it does work but apparently I got my values got mixed up due to formulas in other cells pointing to other values. I think it just needs a minor adjustment.

So actually ranges:
H30 = 180
M109 = Is the total value after adding up everything that's a positive number in C20:L100. In other words: SUMIF(C20:L100,">0")
H29 = Is the remainder left after subtracting H30 from M109 . In other words: H30-M109.

So when H29 <= 50% then trigger the message box half and when H29 = 0 trigger the message box "all".

So sorry about that, my workbook isn't organized very well.
 
Upvote 0
No problem. You should just need to change:

VBA Code:
d = Range("M85").Value / Range("M84").Value
'To
d = Range("M109").Value / Range("H30").Value
 
Upvote 0
Solution

Forum statistics

Threads
1,226,771
Messages
6,192,926
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top