Warning Message for non-zero check figures

DerpDerp24

New Member
Joined
Jul 16, 2019
Messages
3
Hi,

I have a databook with a large number of tabs that we use check figures to ensure consistency from tab to tab. These check figures are just a simple sum function that also subtracts from another tab's subtotal and when everything is working right, should sum to zero. For example, a check figure might look like:

=sum(A1:A10)-sum('Other tab'!A20)

Currently, we have those check figures in yellow throughout the workbook but the issue is that we have to be actively looking at the check figure in order for us to notice there is an issue. I don't believe data validation will work because it only works on input values-- not values resulting from a formula, but surely there is some sort of pop-up warning message I can create that will notify us when a check figure is now non-zero as we are tinkering with the databook?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hello and welcome. You could use conditional formatting of each check figure to change to red when its non-zero.
 
Upvote 0
Hello and welcome. You could use conditional formatting of each check figure to change to red when its non-zero.

Thanks! Been a lurker for a while, but this question has finally gotten me in the door.

Yeah, I've considered conditional formatting as you described, but with how large each tab is in terms of columns and rows, it'd still be an issue of having our window in the right spot of the spreadsheet to be able to identify that the check figure is off with conditional formatting. That is why I'm hoping a warning pop-up is possible.
 
Upvote 0
OK. It would be possible to create some VBA to do what you want, but depending on how often the values go non-zero wouldnt a pop-up be annoying after a while? What about freezing the top row (if it isnt already), and use conditional formatting to change the colour of the row if any check goes non-zero? Without seeing an example of your sheet its difficult to be more specific.
 
Upvote 0
This needs to go into the Workbook module:
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "WS13" And Target.Address = "$D$3" And Target.Value <> 0 Then MsgBox "Sheet " & Sh.Name & " Checkcell " & Target.Address & " in error!"
End Sub
It'll fire each time a change is made anywhere in a workbook, but so quickly, you shouldn't notice - unless it finds an error, of course!!

You'll need to replace "WS13" with the sheet name where your check cell resides, and also "$D$3" with the reference to your actual check cell.
Also, you'll need to adapt it, adding each sheet's name which contains checkcells, and each checkcell's address.
If you only have a few sheets/checkcells, consider putting it into each sheet's code module instead, and adapt accordingly. That's keep down the number of times the code fires.
 
Upvote 0
OK. It would be possible to create some VBA to do what you want, but depending on how often the values go non-zero wouldnt a pop-up be annoying after a while? What about freezing the top row (if it isnt already), and use conditional formatting to change the colour of the row if any check goes non-zero? Without seeing an example of your sheet its difficult to be more specific.

This worked! I appreciate yours and the other commenter's suggestions. If I were more familiar with VBA I may have opted for his solution but I think given the setup of my databook, a frozen row at the top with the auto checks is preferable. Thanks again everyone!
 
Upvote 0
Thanks for the feedback.
You can always give the VBA a go, if you fancy it, or if John's solution doesn't work out for you. Happy to talk you through it, if necessary.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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