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?
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
979
Office Version
2007
Platform
Windows
Hello and welcome. You could use conditional formatting of each check figure to change to red when its non-zero.
 

DerpDerp24

New Member
Joined
Jul 16, 2019
Messages
3
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.
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
979
Office Version
2007
Platform
Windows
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.
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,684
Office Version
365
Platform
Windows
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.
 

DerpDerp24

New Member
Joined
Jul 16, 2019
Messages
3
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!
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,684
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,333
Messages
5,486,215
Members
407,537
Latest member
Frank Nunez

This Week's Hot Topics

Top