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:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,282
Office Version
  1. 2016
Platform
  1. Windows
to start with.. try this & let me know if it serves the initial purpose then will try to add other msgbox when the target is over

VBA Code:
Private Sub Workbook_Open()

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 " & Range("CG375").Value + 1000 - A

End Sub
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
865
Office Version
  1. 365
Platform
  1. Windows
That works fantastic hrayani and made me smile! THANK YOU :biggrin:
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,282
Office Version
  1. 2016
Platform
  1. Windows
you are welcome :)

Sounds like you are ok with 1 msg box... don't you need the other msg box which says congrats you have run 10 miles or so over etc etc
 

Ironman

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

ADVERTISEMENT

Er no, I was hoping for the other one too please ;)
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,282
Office Version
  1. 2016
Platform
  1. Windows
Ok :)
try this

VBA Code:
Private Sub Workbook_Open()

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 " & Range("CG375").Value + 1000 - A
If A > 0 And A <= 10 Then MsgBox "Congratulations! You have now run over " & Range("CG375").Value - A & " miles"

End Sub
 

Ironman

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

ADVERTISEMENT

Heyyyy that is just brilliant!

Thanks a lot! :biggrin:
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
865
Office Version
  1. 365
Platform
  1. Windows
Just a quick one: would it be possible for you to amend your code slightly so there is a thousands separator please? i.e. "xx miles to go until you hit 28,000" (instead of 28000)?

Thanks again!
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,282
Office Version
  1. 2016
Platform
  1. Windows
Just a quick one: would it be possible for you to amend your code slightly so there is a thousands separator please? i.e. "xx miles to go until you hit 28,000" (instead of 28000)?

Thanks again!
VBA Code:
Private Sub Workbook_Open()

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 " & Format(Range("CG375").Value + 1000 - A, "#,#")
If A > 0 And A <= 10 Then MsgBox "Congratulations! You have now run over " & Format(Range("CG375").Value - A, "#,#") & " miles"

End Sub
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
865
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
Private Sub Workbook_Open()

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 " & Format(Range("CG375").Value + 1000 - A, "#,#")
If A > 0 And A <= 10 Then MsgBox "Congratulations! You have now run over " & Format(Range("CG375").Value - A, "#,#") & " miles"

End Sub
 

Forum statistics

Threads
1,148,369
Messages
5,746,295
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