+ Reply to Thread
Results 1 to 12 of 12

Creating Error Messages in MS Excel

  1. #1
    Registered User
    Join Date
    04-20-2008
    Location
    Australia, Victoria, Melbourne
    Posts
    8

    Creating Error Messages in MS Excel

    Hi. I am wondering if it is at all possible (and if so, how) to CREATE your own error message in Microsoft Excel.
    Basicaly, I am wanting to create an error message if two cells' figures do not match up.
    Any help would be greatly appreciated.
    Thanks in advance.

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2013
    Posts
    6,542
    Hi Mike,

    A quick and simple way to do so would be through the use of Data Validation (Data -> Validation menu).

    Let's say you wanted G1 and H18 to match. Go to G1, click Data -> Validation. On the 'Settings' tab, change 'Allow' to "Custom", and in the 'Formula' box below simply type in: =G1=H18

    Click over to the Error Alert tab and set up your own custom error message and choose whether you want your message to "Stop" the user from entering that data or just warn/inform them.

    Do the same for cell H18. Select it, do those steps, but for the formula change it to: =H18=G1.

    Using VBA macros you could further enhance things, but might be more complicated than you need. Let us know if that's something you're interested in.

    Updated formulas, my bad.
    Last edited by Paul; 04-20-2008 at 10:25 PM.

  3. #3
    Registered User
    Join Date
    04-20-2008
    Location
    Australia, Victoria, Melbourne
    Posts
    8
    Thanks pjoaquin.
    I tried this out with the Data -> Validation tool.
    However, upon doing this and entering in dummy data, no error messages are appearing! The two cell's figures are different but no error apeears...
    My second query is: will this error alert appear when, say, cell E16 is entered but cell M52 has not been? This could be an inconvenience as the error might pop up even though I am still progressing towards getting the figure to appear in M52.
    Thanks.

    EDIT: Oh now I know why the error message won't pop up. The figures have to be DIRECTLY typed ino the two cells. With my particular scenario, I have formulae entered into these cells instead, so the validation message won't appear. Perhaps a "VBA macro" is the way to go??
    Last edited by Mike87; 04-20-2008 at 11:39 PM.

  4. #4
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2013
    Posts
    6,542
    This code, placed in the worksheet module for the sheet you're using, will check to see if cells E16 and M52 are equal upon any worksheet change. It will not stop the user from entering an incorrect value, but at least it will prompt them, and it's customizable.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-20-2008
    Location
    Australia, Victoria, Melbourne
    Posts
    8
    This sounds promising... but worksheet module? Where abouts do I enter this code?
    Thanks.

  6. #6
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2013
    Posts
    6,542
    Right-click on the worksheet tab for which you want to use this code, then select 'View Code'.

    Copy and paste this code into the VBA Editor window on the right-hand side (left hand side lists sheets, properties, etc.). Close the VBA Editor and start changing cells. If E16 and M52 don't match, you'll be notified.

  7. #7
    Registered User
    Join Date
    04-20-2008
    Location
    Australia, Victoria, Melbourne
    Posts
    8
    Cheers! But now when both cells match up the error still appears!
    Adding to this, the error message keeps appearing when I am still progressing towards getting the figure to appear in cell M52 - rather annoying.

  8. #8
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2013
    Posts
    6,542
    In my test sheet, when E16 and M52 match, nothing happens. As soon as I change either cell so the two don't match, or change cells that are related to formulas in those two cells, the warning message appears.

    As for 'progressing towards' M52, Excel can't determine when you want the macro to start warning you or other users. You have to tell it when. So when do you want it to start warning you?

    For example, do you only want it to run if all referenced cells in the two formulas have been entered?

    Do you want it to check after you click a command button?

    As long as it's in the Worksheet_Change event, it will trigger on any change whether it's the 1st or 100th.

  9. #9
    Registered User
    Join Date
    04-20-2008
    Location
    Australia, Victoria, Melbourne
    Posts
    8
    Hmmm... I wonder why mine won't work. My error message appears on EVERY occasion - whether the figures match or not! Weird!
    I would like the message to appear not after I type in numbers into cells because I KNOW that obviously the figures will not match up at that point in time [one formula has been done but not the other so, yes, the figures will not match]. I would prefer the message to pop up when all referenced cells have been entered, yes.
    Cheers!

    EDIT: Oh the error message keeps appearing nonetheless if the figure is 6 or under... =S. Otherwise, it works fine.
    Last edited by Mike87; 04-21-2008 at 01:36 AM.

  10. #10
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2013
    Posts
    6,542
    How many cells are we talking about here? For example:
    Please Login or Register  to view this content.
    That checks to see if A1, B1, C17 and M12 have values. If they do, then it will check to see if E16 and M52 match. Change A1, B1, etc. to your cells referenced in the formulas.

  11. #11
    Registered User
    Join Date
    04-20-2008
    Location
    Australia, Victoria, Melbourne
    Posts
    8
    Hrmmm, this is tricky. I have got the message to NOT appear when entering the figures but now the message never appears at all! This is too complicated beacuse I really need the message to appear once figures have been FINALISED. Otherwise, this message will have to appear at EVERY moment that the figures do not match up - pretty annoying.
    Perhaps a button is needed to tell Excel when all figures have been input and finalised by the user - then it can assess whether figures match or not etc.
    Last edited by Mike87; 04-21-2008 at 06:42 PM.

  12. #12
    Registered User
    Join Date
    04-20-2008
    Location
    Australia, Victoria, Melbourne
    Posts
    8
    Still struggling with this particular issue. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1