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!

 

[Michael M]

How often do you want it to appear ??
Why not use a worksheet_activate code so it appears every time you open the worksheet ?

 

[Ironman]

Hi Michael, thanks for replying - I'd really like the message box to appear just once, when the data that triggers the increase has been entered in Col D of another sheet (it then copies to the above range and increases the value of ThisYr). As it will remain greater for the rest of that year I don't want to be reminded every time I open the worksheet.

 

[Michael M]

So, you want the msg box to appear when data is changed in a different sheet. Is that correct ?
If so, include the message in the OTHER sheet code, so when col "D" of that sheet changes the sheet code on that sheet will show the message.

 

[Ironman]

Yes, that's correct.
OK, thanks, that makes sense, but I still don't know how to make the message box pop up only once, as any other change event will trigger it again.

 

[Michael M]

Well, I'm assuming it is only certain data on the other sheet that is relative to the outcome.
So in the sheet code on the other sheet mak it only applicable to changes in that data and nothing else !!
As we don't know what cells are affected on Which sheet, we can't write it for you !
If there is already a worksheet event in the other sheet we would need to see that as well !!

 

[JoeMo]

Yes, that's correct.
OK, thanks, that makes sense, but I still don't know how to make the message box pop up only once, as any other change event will trigger it again.

You may be able to use a static counter so that the msg pops up only once during the period the workbook is open. Closing the workbook will reset the counter to zero so a subsequent opening may trigger the pop-up again if the critical cell(s) are changed manually. As Michael points out you need to provide more info if you are looking for sheet code to be written.

 

[Ironman]

OK thanks a lot Michael and Joe once again, you've both explained that it's not as simple as I thought it might be (yes Michael, I need a beginner's course on VBA...!). It's not a problem though, I'll just use some conditional formatting instead to highlight the cell.

Thanks again both of you for your input.

 

[JoeMo]

OK thanks a lot Michael and Joe once again, you've both explained that it's not as simple as I thought it might be (yes Michael, I need a beginner's course on VBA...!). It's not a problem though, I'll just use some conditional formatting instead to highlight the cell.

Thanks again both of you for your input.

You are welcome - glad we could help.

 

[johnnyL]

Hi Michael, thanks for replying - I'd really like the message box to appear just once, when the data that triggers the increase has been entered in Col D of another sheet (it then copies to the above range and increases the value of ThisYr). As it will remain greater for the rest of that year I don't want to be reminded every time I open the worksheet.

You could use a 'helper cell' somewhere in a sheet to store a value, and then check if that value has been set prior to displaying msgbox.

Might look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If HelperCell <> Something And Range("thisyr") > Range("lastyr") Then MsgBox "Last year's total exceeded!"
End Sub