Msgbox when any one of a number of subtotals has been exceeded

Ironman

Ironman

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

A2:A243 (which is added to regularly) contains values and subtotals for each year.

The subtotals for each year are identified in blue and by a formula in Column B.

I need a message box to appear when the value in cell A245 exceeds a subtotal.

If a trigger cell is needed, then C245 can be used (so I can easily see it), although this will need to change when a new entry is added.

Many thanks!

Book1
AB
210
211136Tue 28/01/2020
212152Wed 05/02/2020
213144Sun 09/02/2020
214166Thu 13/02/2020
215156Mon 17/02/2020
216100Fri 21/02/2020
21798Tue 25/02/2020
21897Sat 29/02/2020
219126Wed 04/03/2020
220142Sun 08/03/2020
221103Thu 12/03/2020
22211TOTAL FOR 2020
223
224135Tue 22/06/2021
22557Sat 26/06/2021
226154Wed 30/06/2021
2277Sun 04/07/2021
228120Mon 12/07/2021
229124Fri 16/07/2021
23051Tue 20/07/2021
231162Sat 24/07/2021
23272Wed 28/07/2021
233101Sun 01/08/2021
2343Thu 05/08/2021
235110Mon 09/08/2021
23640Fri 13/08/2021
23777Tue 17/08/2021
23844Sat 21/08/2021
23968Wed 25/08/2021
2402Sun 29/08/2021
24189Thu 02/09/2021
2424Mon 06/09/2021
24376Fri 10/09/2021
244
24520TOTAL FOR 2021
Iron Man Log
Cell Formulas
RangeFormula
A224:A243,A211:A221A211=RANK(D211,$D$2:$D$245)
A222A222=COUNT(A211:A221)
B222B222="TOTAL FOR " & YEAR(B221)
A245A245=COUNT(A224:A244)
B245B245="TOTAL FOR " & YEAR(B243)
Named Ranges
NameRefers ToCells
Iron_Mans_2020='Iron Man Log'!$A$211:$A$221A222
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A210Cell Value=""textNO
A245Cell Value=""textNO
A244Cell Value=""textNO
A222Cell Value=""textNO
A223Cell Value=""textNO
A2:A6,A211:A221,A182:A208,A177:A179,A153:A174,A149:A150,A138:A146,A112:A135,A98:A109,A90:A95,A87,A78:A84,A75,A68:A72,A58:A65,A55,A50:A52,A38:A43,A35,A23:A32,A9:A20,A224:A243,A46:A47Cell Value=1textYES
A2:A6,A211:A221,A182:A208,A177:A179,A153:A174,A149:A150,A138:A146,A112:A135,A98:A109,A90:A95,A87,A78:A84,A75,A68:A72,A58:A65,A55,A50:A52,A38:A43,A35,A23:A32,A9:A20,A224:A243,A46:A47Cell Value=2textYES
A2:A6,A211:A221,A182:A208,A177:A179,A153:A174,A149:A150,A138:A146,A112:A135,A98:A109,A90:A95,A87,A78:A84,A75,A68:A72,A58:A65,A55,A50:A52,A38:A43,A35,A23:A32,A9:A20,A224:A243,A46:A47Cell Valuebetween 3 and 10textYES
A2:A6,A211:A221,A182:A208,A177:A179,A153:A174,A149:A150,A138:A146,A112:A135,A98:A109,A90:A95,A87,A78:A84,A75,A68:A72,A58:A65,A55,A50:A52,A38:A43,A35,A23:A32,A9:A20,A224:A243,A46:A47,A246:A531Cell Value=""textNO
Cells with Data Validation
CellAllowCriteria
A245Whole number=12345
A222Whole number=12345
 
Sort by date Sort by votes
Ok, firstly you can't have 2 worksheet_change events in the one sheet.....:cool:
What is in the other event !!
and for the Range("IRONMAN_RUNS_TOTAL") ...try using
VBA Code: 
Set v = Range("IRONMAN_RUNS_TOTAL")
If v = 200 Then
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Here's the table with actual column/row numbers, if this helps?
Book1
ABCDEF
223TOTYEARRK/23No. >3HRSYEARRANK/23
22451984140198412
2251219865219867
2261019878119879
22711988200198812
22861989120198912
22921990180199012
23031991160199112
23111993200199312
2328199410519941
23351996140199612
23411998200199812
2357200011120009
23612001200200112
23762002120200212
2381220035520031
2392420042520041
240920059220057
24122006180200612
2422220073420074
2433200816120089
2442720191420194
24511202070202012
2462020214420214
247 
248198<<< TOTAL No. IRON MAN RUNS 1984-2021 
Iron Man Log
Cell Formulas
RangeFormula
C223C223="RK/"& COUNT(A224:A247)
C224:C246C224=RANK(A224,$A$224:$A$246)
A246A246=COUNT(A202:A222)
D246D246=COUNTIF(D202:D222,">="&TIME(3,0,0))
A248A248=COUNT(A2:A222)
B248B248="<<< TOTAL No. IRON MAN RUNS 1984-" & YEAR(B221)
F223F223="RANK/"& COUNT(A224:A247)
F224:F246F224=RANK(D224,$D$224:$D$246)
F247F247=IF(C246=4,"","WELL DONE, YOU'VE JUST IMPROVED THIS YEAR'S RANKING! NOW AMEND THE FORMULA IN THIS CELL.")
F248F248=IF(F246=4,"","WELL DONE, YOU'VE JUST IMPROVED THIS YEAR'S RANKING! NOW AMEND THE FORMULA IN THIS CELL.")
Named Ranges
NameRefers ToCells
Iron_Mans_2020='Iron Man Log'!$A$190:$A$200A248
IronManLogLastYrTot='Iron Man Log'!$A$245F223, C223:C246
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F247Cell Valuecontains "RANKING"textNO
D247Cell Valuecontains "RANKING"textNO
C247:E247Expression=$B$247="WELL DONE, YOU'VE JUST IMPROVED THIS YEAR'S RANKING! NOW AMEND THE FORMULA IN THIS CELL."textNO
B247Cell Valuecontains "RANKING"textNO
F248Cell Valuecontains "RANKING"textNO
D224:D246Cell Valuetop 1 valuestextNO
D224:D246Cell Valuetop 2 valuestextNO
D224:D246Cell Valuetop 3 valuestextNO
A224:A246Cell Valuetop 1 valuestextNO
A224:A246Cell Valuetop 2 valuestextNO
A224:A246Cell Valuetop 3 valuestextNO
D35,D27,D29,D37,D117,D222,D248,D7,D20,D31,D33,D40:D41,D43,D47,D49,D58,D64,D66,D74,D76,D83,D96,D121,D131,D134,D157,D161,D189,D201Cell Value>=0.1459textNO
D35,D27,D29,D37,D117,D222,D248,D7,D20,D31,D33,D40:D41,D43,D47,D49,D58,D64,D66,D74,D76,D83,D96,D121,D131,D134,D157,D161,D189,D201Cell Valuebetween 0.125 and 0.1458textNO
D35,D27,D29,D37,D117,D222,D248,D7,D20,D31,D33,D40:D41,D43,D47,D49,D58,D64,D66,D74,D76,D83,D96,D121,D131,D134,D157,D161,D189,D201Cell Valuebetween 0.08333333333 and 0.12495textNO
A132:A133,A75,A65,A48,A32,A248:A533,A190:A200,A202:A221,A162:A188,A158:A160,A135:A156,A122:A130,A97:A120,A84:A95,A77:A82,A67:A73,A59:A63,A50:A57,A44:A46,A41:A42,A34:A39,A21:A30,A8:A19,A2:A6Cell Value=""textNO
Cells with Data Validation
CellAllowCriteria
D246Any value
C224:C245Whole number=454
C246Whole number=454
F224:F246Whole number=454
F247:F248Whole number=124265
A224:A238Whole number=3364
A239Whole number=3364
A240:A244Whole number=3364
A245Whole number=12345
A246Whole number=12345
A248Whole number=12345
B248Whole number=546
 
Upvote 0
Ahhh, thanks for that - I've learnt something else from you :)

You mean replacing
VBA Code: 
If Range("IRONMAN_RUNS_TOTAL") = 200 Then
with
VBA Code: 
Set v = Range("IRONMAN_RUNS_TOTAL")
If v = 200 Then
If Sheets("Training Log").Range("H8") = "" Then
MsgBox "Congratulations Paul! You've just run your 200th Iron Man Run!", vbInformation, "Iron Man Runs"
Sheets("Training Log").Range("H8") = "1"
End If
End If
 
Upvote 0
Yes
AND
did you note the comment regarding
Rich (BB code): 
you can't have 2 worksheet_change events in the one sheet.
 
Upvote 0
Thanks

Yes but I don't really understand that, as it worked when I just tested it. Is this code preventing your code from running? When I commented it out, your code still wouldn't run :unsure:
 
Upvote 0
Er, OK, so do I need to add
VBA Code: 
    Application.EnableEvents = False
xxxxx
    Application.EnableEvents = True
 
Upvote 0
Hi Michael, yes I followed that exactly when you posted and just now as well, and with the other code commented out, it still does nothing.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Max of close values
2
Replies
13
Views
621
rdubs
R
C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
875
NdNoviceHlp
NdNoviceHlp
B
  • Question
Calculating Annualized returns of 1 year, 3 years, 5 years, and 10 years from the data of 132 months/ 11 Years
Replies
0
Views
464
Brandon_333
B
Z
  • Question
Attrition forecast for next 3 months?
Replies
0
Views
401
zinah
Z
C
  • Solved
Issue with 00:00 time when using an IF function
Replies
3
Views
395
jtakw
jtakw

Forum statistics

Threads
1,214,979
Messages
6,122,550
Members
449,088
Latest member
davidcom

Share this page

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
Back
Top