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

...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

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

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

Originally Posted by cytop

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..

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

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

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

Originally Posted by avk

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.

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.

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.

@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.

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.

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.