Display msgbox when greatest values achieved YTD

[zoso]

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!

 

[just_jon]

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]

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!