Display Message Box only once when value exceeded

[Ironman]

Hi

I've created a simple message box when Col B in the bottom row of the expanding range below exceeds Col B in the row above it. The two cells are named ranges (I've placed them in cells so it's clear what they do - they're not actually in those cells) and it looks like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Range ("thisyr") > Range ("lastyr") then msgbox "Last year's total exceeded!"
End Sub

Book1.xlsm
	A	B
1	YEAR	TOTAL
2	1984	5
3	1986	12
4	1987	10
5	1988	1
6	1989	6
7	1990	2
8	1991	3
9	1993	1
10	1994	8
11	1996	5
12	1998	1
13	2000	7
14	2001	1
15	2002	6
16	2003	12
17	2004	24
18	2005	9
19	2006	2
20	2007	22
21	2008	3
22	2019	27
23	2020	11
24	2021	32
25
26	TOTAL	210
27
28	Last Yr	11
29	This Yr	32
Sheet1

Cell Formulas
Range	Formula
B26	B26	=SUM(B2:B25)
B28	B28	=LastYr
B29	B29	=ThisYr

Named Ranges
Name	Refers To	Cells
LastYr	=OFFSET(Sheet1!$B$24,-1,0)	B26
ThisYr	=OFFSET(Sheet1!$B$25,-1,0)	B28, B26

What I'm after is a bit more code so the message box only pops up once.

Many thanks!

 

[Ironman]

Thanks a lot for your suggestion Johnny, that's the only way I knew how to do it as well, by creating a '1' in a cell after the event and then clearing it on exit (although the next time the workbook is opened and the sheet changes it will then reappear and trigger the message box again).

 

[johnnyL]

Why are you contemplating clearing the flag on exit? Leave it there. Bury the flag somewhere out of the way and leave it there. You could put it on a veryhidden sheet if you want to.

 

[Ironman]

Because if I do that, it will never activate again because the code will only run if the cell is blank.

 

[johnnyL]

One of us is confused.

What I am suggesting as an approach is replacing:

    If Range("thisyr") > Range("lastyr") Then MsgBox "Last year's total exceeded!"

with something like:

    If HelperCell <= CurrentYear And Range("thisyr") > Range("lastyr") Then
        MsgBox "Last year's total exceeded!"
'
        HelperCell = CurrentYear + 1
    End If

If you don't think that will work, please explain how it would fail to work.

HelperCell would be an address somewhere and CurrentYear would be a variable that is calculated.

 

[Ironman]

OK, what I've been using is this:

Private Sub Worksheet_Change(ByVal Target As Range)

'**UNCOMMENT JAN 1 ANNUALLY**
'04.11.2021 Msgbox activated if this year's total exceeds last year's
'If Range("IronManLogThisYr").Value > Range("IronManLogLastYrTot").Value Then
'If Sheets("Training Log").Range("H3") = "" Then
'MsgBox "Congratulations!" & vbNewLine & vbNewLine & "You've now run more Iron Man runs than last year!", vbInformation, "Last year's total beaten"
'Sheets("Training Log").Range("H3") = "1"
'End If
'End If
'End Sub

There are other subs in the Change Event so to avoid it activating every time another cell changes, it has to be commented out once it's activated until Jan 1 in the next year.

 

[johnnyL]

This is the code I tested and the msgbox only popped up one time:

    Dim HelperCell      As Range
    Dim wsHelperSheet   As Worksheet
'
    Set wsHelperSheet = Sheets("Sheet1")                    ' <--- Set this to desired Helper sheet
    Set HelperCell = wsHelperSheet.Range("A1")              ' <--- Set this to desired cell address to store the flag
'
    CurrentYear = Year(Date)                                ' Get current year
'
    If HelperCell <= CurrentYear Then                       ' If flag < or = to CurrentYear then ...
        HelperCell = CurrentYear + 1                        '   Increment flag so msgbox will not be executed until the next year
        MsgBox "Helper cell Updated"                        '   Display message to user
    End If

The msgbox will not have a chance to popup until next year when the code satisfies the 'HelperCell <= CurrentYear' portion of the code.

 

[Ironman]

Hey that's brilliant Johnny, thanks ever so much.

Could I just ask you to tell me where your code fits into the code I posted (#16) please? I will of course delete the references I made to H3 and use your code to reference that instead.

Thanks again!

 

[johnnyL]

You mean this? :

    Dim HelperCell      As Range
    Dim wsHelperSheet   As Worksheet
'
    Set wsHelperSheet = Sheets("Training Log")                                                      ' <--- Set this to desired Helper sheet
    Set HelperCell = wsHelperSheet.Range("H3")                                                      ' <--- Set this to desired cell address to store the flag
'
    CurrentYear = Year(Date)                                                                                        ' Get current year
'
    If HelperCell <= CurrentYear And Range("IronManLogThisYr").Value > Range("IronManLogLastYrTot").Value Then      ' If flag < or = to CurrentYear and
'                                                                                                                   '   ThisYr total> LastYr total then ...
        HelperCell = CurrentYear + 1                                                    '   Increment flag so msgbox will not be executed until the next year
        MsgBox "Congratulations!" & vbNewLine & vbNewLine & "You've now run more Iron Man runs than last year!", _
            vbInformation, "Last year's total beaten"                                                               '   Display message to user
    End If

 

[Ironman]

Exactly that - brilliant, that works perfectly, thank you so much for that Johnny!

 

[johnnyL]

And they said it couldn't be done. Glad to help @Ironman.