Message Box when value is within 100 of the next 1000

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
865
Office Version
  1. 365
Platform
  1. Windows
Hi

Sheet 'Daily Tracking' Cell CG375 contains a value, currently 27851 and is increasing.

What I'd be really grateful for is a message box that is triggered when I open the workbook if/whenever the value in Sheet 'Daily Tracking' Cell CG375 is within 100 of the next 1000 i.e. it will next trigger between 27900 and 28000. It will then trigger between 28900 and 29000 and so on and in each case it calculates the difference between the two values.

e.g. for 27900 the message would be "100 miles to go until you hit 28,000 miles".

Also, if possible, when the value is equal to or greater than the 1000 by up to 10 e.g. 28000 - 28010 a different message is triggered e.g. "Congratulations, you have now run over 28,000 miles"

I'd also appreciate it if the value could be rounded up to the nearest whole number.

Many thanks!
 
Last edited:

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
865
Office Version
  1. 365
Platform
  1. Windows
That's brilliant, thanks once again hrayani👍
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
865
Office Version
  1. 365
Platform
  1. Windows
Hi hrayani, I have just opened my workbook and I got the message "89 miles to go until you reach 89" :(

Would you be able to locate the error please?

Thanks again!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
What is currently in CG375...or "milessince1981"
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
865
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Michael, it's a sum formula =SUM(B375:CC375).

The value is currently 27,878 and I assumed all was well until I unknowingly tested it in another situation with an increased value that triggered the message as above.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
If the current value is 27,878, it shouldn't trigger the msgboxes
It only triggers if the value is 27901 to 28010...works fine for me
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
865
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Sure, but it triggered when I inadvertently increased the value and that's when I saw it wasn't working as it should.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
What was the inadvertent value ??
How did you increase the value, as the code provided will only work when the workbook is opened NOT when a vlaue changes after open
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
865
Office Version
  1. 365
Platform
  1. Windows
I understand. I just increased the value to 27,900, saved and closed, then re-opened. Then this msg: "100 miles to go until you hit 100".
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
In that case I'm guessing the "Daily Treacking" sheet isn't the active sheet at workbook_open?
Try this code instead
VBA Code:
Dim A As Integer
A = Sheets("Daily Tracking").Range("CG375").Value Mod 1000
If 1000 - A <= 100 Then MsgBox 1000 - A & " miles to go until you hit " & Sheets("Daily Tracking").Range("CG375").Value + 1000 - A
If A > 0 And A <= 10 Then MsgBox "Congratulations! You have now run over " & Sheets("Daily Tracking").Range("CG375").Value - A & " miles"
 
Solution

Forum statistics

Threads
1,148,370
Messages
5,746,303
Members
424,006
Latest member
Metal_warrior

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
Top