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

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
547
Office Version
  1. 2019
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
547
Office Version
  1. 2019
Platform
  1. Windows
I've wisely decided to spend some time tidying up the above and the relevant data now looks like this:
Iron Man Log DELETE AFTER 12.09.2021.xlsm
ABCDEF
248TOTYEARRANK/23No. >3HRSYEARRANK/23
24951984140198412
2501219865219867
2511019878119879
25211988200198812
25361989120198912
25421990180199012
25531991160199112
25611993200199312
2578199410519941
25851996140199612
25911998200199812
2607200011120009
26112001200200112
26262002120200212
2631220035520031
2642420042520041
265920059220057
26622006180200612
2672220073420074
2683200816120089
2692720191420194
27011202070202012
2712020214420214
Iron Man Log
Cell Formulas
RangeFormula
C249:C271C249=RANK(A249,$A$249:$A$271)
F249:F271F249=RANK(D249,$D$249:$D$271)
Cells with Data Validation
CellAllowCriteria
A270Whole number=12345
A271Whole number=12345

I need 2 message boxes

1) When the value in C271 changes, it should say "You've just surpassed the number of runs for (Column B cell value that has just been outranked)!
(VB new line) Rank increased to (value in C271)"
2) When the value in F271 changes, it should say "You've just surpassed the number of 3 hour runs for (Column E cell value that has just been outranked)!
(VB new line) Rank increased to (value in F271)"

Many thanks!
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,915
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
So, just guessing here, as you haven't provided what that value may be....AND what about ties in the ranking ???
1) When the value in C271 changes, it should say "You've just surpassed the number of runs for (Column B cell value that has just been outranked)!
(VB new line) Rank increased to (value in C271)".....the answer for this one will be B267 ??
2) When the value in F271 changes, it should say "You've just surpassed the number of 3 hour runs for (Column E cell value that has just been outranked)!
(VB new line) Rank increased to (value in F271)".....the answer for this one will be E264....??
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
547
Office Version
  1. 2019
Platform
  1. Windows
Hi Michael, apologies for being unclear.

Good question once again - in that case, say "Rank increased to x (joint)"?

1. Yes, it will be B267 (previously ranked 3rd)

2. Yes, it will be E264

As a side point, you probably surmised that A249:270 and D249:270 are values for past years so they will never change.

Thanks again!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,915
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Maaybe this for question 1....you can do the rest !!
VBA Code:
Sub MM1()
Dim r As Long, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr - 1 To 249 Step -1
If Cells(r, 3) = Cells(lr, 3) - 1 Then
    MsgBox "You've just surpassed the number of runs for " & Cells(r, 3).Offset(0, -1).Value
    Exit For
End If
Next
End Sub
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
547
Office Version
  1. 2019
Platform
  1. Windows
Many thanks Michael, just a couple of things -

1. Should this run as a worksheet_change event? If so, will this work:
VBA Code:
Private Sub Worksheet_Change(ByVal target As Range)
Dim r As Long, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr - 1 To 249 Step -1
If Cells(r, 3) = Cells(lr, 3) - 1 Then
    MsgBox "You've just surpassed the number of runs for " & Cells(r, 3).Offset(0, -1).Value
    Exit For
End If
Next
End Sub

2. You've specified row 249 - this number will change with more rows being added above. If I used a named range instead (say "ABC") to overcome this, how would the code have to look?

3. As I haven't run the code yet, I'm guessing it will only work for Q1 and I'm adapting it for Q2?

Thanks again!
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,915
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

I still think there may be issues with this code...haven't tested as yet !

1. worksheet_change
2. You've specified row 249 - won't you be adding extra dat at row 271 onwards ..are you going back beyond 1984 ?

3. As I haven't run the code yet, I'm guessing it will only work for Q1 and I'm adapting it for Q2?....Yes !
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
547
Office Version
  1. 2019
Platform
  1. Windows
1. Noted, thanks
2. Yes, but as data similar to the below is being inserted above row 249 then that row number will no longer be correct in another 3 days ;)

4Mon 06/09/202115.13:46:2415:00
76Fri 10/09/202110.62:32:4214:24
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,915
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
So how do we know where the first row of this table will be...that seems a bit backward to me....always thought the latest would be added after row 271
I think this one is more accurate
VBA Code:
Sub MM3()
Dim r As Long, lr As Long, n As Integer
lr = Cells(Rows.Count, "A").End(xlUp).Row
n = Evaluate("=MAX(IF(C249:C" & lr - 1 & "<C" & lr & ",C249:C" & lr - 1 & ",""""))")
For r = lr - 1 To 249 Step -1
If n = Cells(r, 3) Then
    MsgBox "You've just surpassed the number of runs for " & Cells(r, 3).Offset(0, -1).Value
    Exit For
End If
Next
End Sub
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
547
Office Version
  1. 2019
Platform
  1. Windows
I guess you're right Michael, but the entries above the table are in chronological order, going back to 1984 - I don't know where else the table could go, other than on a new sheet.

Maybe if I relocated it at the top, to the right of the data, would that be better?
 

Forum statistics

Threads
1,141,581
Messages
5,707,196
Members
421,497
Latest member
philipg

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