Display msgbox when greatest values achieved YTD

zoso

Well-known Member
Joined
Oct 23, 2003
Messages
725
I have a worksheet called Training Log, which is a running diary.

Cells A1:A11 are filled with misc formulas. Cells A12 onwards contain dates from 1998 to the present day.

I usually add an entry every day but this may not always be so and am currently down to A1702 - I eventually expect cells will be filled with dates to no more than A20000.

The current year starts in row 1680, but if possible, I'd like the code to be able to determine if we're in a new year automatically so I can avoid amending the code in future years.

Column C cells A12 onwards contain distance run in number format to 1 decimal place.

Column D cells A12 onwards contain hours run in the format: [h]:mm:ss

What I would be really grateful for, if this is possible, is for a msgbox to popup in 2 instances:

a) when the DISTANCE I have run is greater than at any date in the current year to date.

b) when the TIME I have run is longer than at any date in the current year to date.

The msgboxes could be something like:

a) Congratulations - you've just run the furthest distance you've run all year

b) Congratulations - you've just been running for the longest session since the start of the year.

Also, I suppose it goes without saying, but I am keen to avoid the code looping, so when I have clicked the msgbox it doesn't keep appearing - maybe if it just ran once until the figure increases later in the year, or something like that?

If it helps at all, Column A cell A2 always contains todays date.

Hope this is possible - and if so, thanks a lot in advance!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Howdy - think you'd then handle this in a change event - you've already got one, I think, so this would be an add to.

Could use MATCH with 3rd agr=0 to find 1st entry of the year, MATCH with 1 as 3rd arg to find last.

Then, if current entry = MAX in the above range, shoot out a msgbox; if not, do nothing.

Out of time, but might help to post that sheet's Worksheet_Change macro.
 

zoso

Well-known Member
Joined
Oct 23, 2003
Messages
725
Here's the code for the change event on the worksheet:


code:
--------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

CALC = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False


'Messages are contained here:
Call ImportantMessages


Application.Calculation = CALC
Application.Calculate
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Sub ImportantMessages()

If Range("IRONMAN_YTD") = 30 Then
If [H1] = "" Then
MsgBox "Congratulations Paul! You've now completed 30 Iron Man runs this year!", vbInformation, "Iron Man Runs"
Application.EnableEvents = False
[H1] = "1"
Application.EnableEvents = True
End If
End If

If Range("IRONMAN_YTD") = Range("IRONMAN_LAST_YEAR") Then
If [I1] = "" Then
MsgBox "Congratulations Paul! You've now completed the same number of Iron Man runs as you did for the whole of " & Year(Now) - 1, vbInformation, "Iron Man Runs"
Application.EnableEvents = False
[I1] = "1"
Application.EnableEvents = True
End If
End If

End Sub
--------------------------------------------------------------------------------

Hope someone can help!

Thanks!
 

Forum statistics

Threads
1,148,053
Messages
5,744,534
Members
423,881
Latest member
Nguyen Vu

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