+ Reply to Thread
Results 1 to 18 of 18

Is it possible to have a alert box pop up if conditions are not met?

  1. #1
    Registered User
    Join Date
    03-09-2017
    Location
    Monrovia
    MS-Off Ver
    2013
    Posts
    57

    Is it possible to have a alert box pop up if conditions are not met?

    I want to know if there is any way where I can have an alert box popup if a certain conditions are not met without using VBA if possible.

    Premise: I have a column F(around 100 rows) which contains the formula =SUM(A1+B1+E1) . I want the alert message " Your value is less than 40" to pop up If the sum is less than 40.

    I want this to happen automatically without clicking any buttons that is why I don't want to use VBA.

    Thanks in advance

  2. #2
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Is it possible to have a alert box pop up if conditions are not met?

    ...without using VBA if possible
    Formulas cannot display a pop-up so VBA is the only answer if that is what you really want. The usual way to highlight values that do not match criteria is to use Conditional Formatting to apply a colour to the cell background/text.

    You also don't need to click a button to run VBA code - it can run automatically when events like selecting a cell or the worksheet is recalculated occur

  3. #3
    Registered User
    Join Date
    03-09-2017
    Location
    Monrovia
    MS-Off Ver
    2013
    Posts
    57

    Re: Is it possible to have a alert box pop up if conditions are not met?

    Do you have any examples that show the VBA running automatically when a sum is calculated?

  4. #4
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Is it possible to have a alert box pop up if conditions are not met?

    Select complete column "F"
    >Go to "Data Validation" > Select "Data Validation
    >In Allow Select : "Custom"
    >In Formula enter formula :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    >Right click on "Apply those changes to all other cells with the same setting"
    >In Error Tab > In Style : Select "Stop"
    >In Title & Error Message Enter : "Your value is less than 40"
    >Click "ok"


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  5. #5
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Is it possible to have a alert box pop up if conditions are not met?

    I'd strongly advise against going that route. You do not know which cell was changed using the Calculate event so have to check ALL cells in the range which means that you get a message box every time any change is made until all the cells match the condition - it'll take about 10 seconds flat to get totally annoyed with this.

    The attached has code that checks F2:F24 and pops a msgbox, it also has conditional formatting in Col G. It also uses 100 as the criteria - got to leave something for you to do...
    Attached Files Attached Files
    Last edited by cytop; 04-27-2017 at 04:27 AM.

  6. #6
    Registered User
    Join Date
    03-09-2017
    Location
    Monrovia
    MS-Off Ver
    2013
    Posts
    57

    Re: Is it possible to have a alert box pop up if conditions are not met?

    Hi Avk,
    I tried the steps mentioned by you, But I didn't get any popup message when the total was less than 40.
    error.JPG

  7. #7
    Registered User
    Join Date
    03-09-2017
    Location
    Monrovia
    MS-Off Ver
    2013
    Posts
    57

    Re: Is it possible to have a alert box pop up if conditions are not met?

    Quote Originally Posted by cytop View Post
    The attached has code that checks F2:F24 and pops a msgbox, it also has conditional formatting in Col G. It also uses 100 as the criteria - got to leave something for you to do...
    I don't think you have attached the code..

  8. #8
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Is it possible to have a alert box pop up if conditions are not met?

    I think I did - did you look in the Sheet1 class module?

  9. #9
    Registered User
    Join Date
    03-09-2017
    Location
    Monrovia
    MS-Off Ver
    2013
    Posts
    57

    Re: Is it possible to have a alert box pop up if conditions are not met?

    I meant to say I couldn't find any attachments in your posts.
    no Attachments.JPGno Attachments2.JPG

  10. #10
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Is it possible to have a alert box pop up if conditions are not met?

    Weird... when I viewed the post after replying (initially) the attachment was there... and then it wasn't but it is now (again)... Apols

  11. #11
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Is it possible to have a alert box pop up if conditions are not met?

    Are you entering formula before data validation.
    Refer attach file.
    Applicable in range "F1:F100"
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    03-09-2017
    Location
    Monrovia
    MS-Off Ver
    2013
    Posts
    57

    Re: Is it possible to have a alert box pop up if conditions are not met?

    Quote Originally Posted by avk View Post
    Are you entering formula before data validation.
    Refer attach file.
    Applicable in range "F1:F100"

    I entered the formula before data validation.

    This is just a small part for my previous question which I am trying to make it user friendly. Here for the timeuti-499 for the final column that is V, has got formulas that add the values from columns L,T and U.
    Here in timeuti files my friends will only be adding their time utilization details for the current week. So if the rows are blank I don't want any prompts to happen. Can this be achieved when they save the file? Suppose they have entered the details for the particular date and when they save the file a prompt opens saying they have not entered 40 hours and if they still want to continue to save?

    Since there will be lots of timeuti files I can't ask my friends to add the formula everytime they enter their time utilization. This is also the reason I wanted to avoid using macros.

  13. #13
    Registered User
    Join Date
    03-09-2017
    Location
    Monrovia
    MS-Off Ver
    2013
    Posts
    57

    Re: Is it possible to have a alert box pop up if conditions are not met?

    I do have this code, but I have not been able to get it to work when saving.
    Please Login or Register  to view this content.

  14. #14
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Is it possible to have a alert box pop up if conditions are not met?

    You need to understand where that code should be placed.

    The Workbook_BeforeSave event procedure should be placed in the ThisWorkbook class module. The Worksheet_Change event procedure belongs in a worksheet class module. The Change event is private to the sheet that contains it so cannot be called directly from the BeforeSave event... If the BeforeSave event code is not in the ThisWorkbook class then it simply won't run before the file is saved.

    In any case the Change event takes a parameter of type Range which you do not pass so the code won't even compile, never mind run but not work.

    If you want to continue just checking before saving, I'd suggest you combine that with the Conditional Formatting to highlight formulas that do not match the criteria. That way the user does have some indication the file won't save.

  15. #15
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Is it possible to have a alert box pop up if conditions are not met?

    Dear chunkyp, Kindly attach sample file with required desired result (with Before Data & After Data)

  16. #16
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Is it possible to have a alert box pop up if conditions are not met?

    @avk

    I think you're misunderstanding things here, The OP wants a check on Col F that the total is > 40. The cells contain a formula which precludes the use of Data Validation.

  17. #17
    Registered User
    Join Date
    03-09-2017
    Location
    Monrovia
    MS-Off Ver
    2013
    Posts
    57

    Re: Is it possible to have a alert box pop up if conditions are not met?

    I have attached the timeuti file here.
    What this sheet does is get the total time spent between columns B to K and save them in L for each week. Similarly the contents of columns M to S are added and saved in column T. Finally I add columns L+T+U to get total hours in column V.
    Now this file will be updated every week for the particular day and sent to me. Sometimes what happens is that my classmates won’t code the time utilization sheet for 40 or more hours in which case I send it back to them to re-enter and send it back.
    What I wanted to do was when they enter less than 40 hours for say tomorrow I want an alert to popup stating that they have not coded their sheet for 40 hours and to please recode it again so that the time utilization is 40 hours or more.
    I was wondering, when my friends try to save their weekly data, if it was possible for the alert to popup rather than when they enter data for every cell. Mostly I want to avoid using any buttons in this sheet.
    Attached Files Attached Files

  18. #18
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Is it possible to have a alert box pop up if conditions are not met?

    My suggestion - Before the workbook is closed the number of cells on the Utilization worksheet, in Col v, starting in Row 3 for however many rows are used, are counted for values < 40 and > 0.

    If any are found then a message is displayed (see code) but the user can still save the file if needed. Nothing is going to annoy users more than being forced to complete inputs before closing a file...
    Please Login or Register  to view this content.
    That goes in the ThisWorkbook module. Adding it anywhere else just means it won't run.

    I would also suggest conditional formatting using the same criteria ( >=40, > 0) in Col V to highlight to the user that input is not complete on a particular line.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 01-14-2016, 03:53 PM
  2. Send an ALERT email to a GMAIL address if conditions are met in a specific cell
    By zeegerman in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-25-2015, 04:56 PM
  3. Replies: 0
    Last Post: 10-10-2013, 05:14 AM
  4. [SOLVED] Alert - When 3 conditions are met on the same row
    By Alvin Hunter in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-17-2012, 10:09 AM
  5. New message alert... doesn't ALERT me very well
    By Xx7 in forum Outlook Formatting & Functions
    Replies: 6
    Last Post: 07-20-2011, 03:54 PM
  6. Macro to find value, and alert if conditions arent met
    By JSD133 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-25-2011, 05:30 AM
  7. Alert when conditions are met
    By KingKarl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-20-2005, 05:06 AM

Tags for this Thread

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