Warning Message for non-zero check figures
Results 1 to 8 of 8

Thread: Warning Message for non-zero check figures
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Warning Message for non-zero check figures

    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?

  2. #2
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    590
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Warning Message for non-zero check figures

    Hello and welcome. You could use conditional formatting of each check figure to change to red when its non-zero.
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Warning Message for non-zero check figures

    Quote Originally Posted by jmacleary View Post
    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.

  4. #4
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    590
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Warning Message for non-zero check figures

    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.
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  5. #5
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Warning Message for non-zero check figures

    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.
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  6. #6
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Warning Message for non-zero check figures

    @DerpDerp24 Did this work for you?
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  7. #7
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Warning Message for non-zero check figures

    Quote Originally Posted by jmacleary View Post
    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!

  8. #8
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Warning Message for non-zero check figures

    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.
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •