pop up box when formula result returns a certain value

[Kat81]

Hi guys,

I'm a total novice so I apologise in advance for poor Excel lingo and the like..

I have a spreadsheet in which column H returns a numeric value based on a simple formula (just adding and subtracting values from a couple of different cells) ie: the user does not enter values into this cell manually.

H column values are cumulative by row based on the values entered into the corresponding cells in columns F or G.

The value in the H column should be below 40.

I am looking for a pop up box that is generated when the cell value is equal to or greater than 40 after an entry is made into the corresponding F or G cell.

I did find the example below, but I don't know how to make it apply to the appropriate cell range in column H. The range I need it to look at is H9:H60. It does work if I enter a vlue of greater than 40 in cell H9.

Please Login or Register  to view this content.

If anyone has any suggestions I'd be most appreciative!!

Remember, I don't know what I'm doing so please be gentle!

Kat

[JBeaucaire]

This should do it:

Please Login or Register  to view this content.

Please take a note of the forum rules, CODE tags are required when posting VBA. Thanks.

[Kat81]

Thanks Jerry,

(thanks also for pointing out the CODE tags requirement - will make sure I do this in future)

The code you suggested definitely works in that once the value in column H went over 40, the dialog box popped up, the only problem is that as I enter subsequent values in column F or G (irrespective of the change to the value in column H - even if it drops back below 40) the dialog box still pops up. It is obviously looking for any instance of values in column H being over 40. Is it possible for the code to only look at the final value in column H - ie: if there are values in column H between rows 9 and 15, then only check the value in row 15 and ignore instances of greater than 40 in cells 9 - 14?

Columns F and G are both manual data entry (numeric values). A value in column F increases the value in the correspodning cell in column H, whereas a value added to column G will be substracted from column H. Essentially column H is a running tally (total range between H9 and H60).

Thanks again for your help!

Kat

[JBeaucaire]

Why not make it simple, then. In an empty cell, let's say Z1, put a formula that gives current value of all the data entered:

=SUM(F:F)-SUM(G:G)
(or whatever range you want to set)

Then the macro need only watch the value of that one cell:

Please Login or Register  to view this content.

[Kat81]

That's awesome!

So simple I wish I'd thought of it myself! haha!!

Thanks for your help Jerry - I really appreciate it

[JBeaucaire]

You wanna really get fancy, you don't even have to use a cell on the sheet. You can create something called a "Named Formula" that's hidden in the background and you can check it's value at any time.

1) Press CTRL+F3 to open the Name Wizard window

2) Enter a new name, let's call it ToilLimit (notice it's two words but no space? Spaces aren't allowed in named formulas)

3) In the RefersTo: line enter that formula I gave you, but add $ signs:

=SUM($F:$F)-SUM($G:$G)

4) Now you can get the current value of ToilLimit several ways:

- in a cell the formula would be =ToilLimit

- Change the macro given to check that hidden value:

Please Login or Register  to view this content.

Anyway,
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Kat81]

I had no idea you could even do that!

I think that will come in very handy not just for this issue, but for others in the future.

Will close the thread off now - thanks heaps for your help!

Kat