On Saturday, July 8, 2017 at 2:20:37 AM UTC-7, Claus Busch wrote:
Hi Howard,

Am Fri, 7 Jul 2017 15:52:26 -0700 (PDT) schrieb L. Howard:

If cell I20 is greater than 100 then "A MsgBox warning" is needed.

Formula in I19 is =SUM(I5:I18)
Formula in AE19 is =SUM(AE4:AE18)

Formula in I20 is =SUM(I19,AE19)

I have these two (Change & Calculate) macros in the sheet module. (below)
Both work if I20 100, I get a msgbox warning from each macro.

I expect to add more If statements in the SheetChange macro as well as there may also be more Calculations to monitor in others cells on the sheet that have formulas.

What is the best practice in this situation?
All in the Change macro or all in the Calculate macro?

It takes an entry on the sheet (Columns I or AE) to affect the value of I20, therefore the Change Event seems to work fine.

It takes a calculation on the sheet to affect the I20 value and therefore the Calculate Event seems to work fine.

Also at issue is the values in columns I and AE are percentages (%). What is the proper method to format/alter the cells/column/formulas to prevent 80 from reading out as 800%?

is there no way to avoid entries with semicolon or comma?
You can format columns I and AE as percent. Which values do the users
enter in that columns?
When I20 is formatted as percent you have to check against 1.



Regards
Claus B.
--

Hi Claus,

Ref the commas and semi-colons, apparently that is a problem with the users in columns B C D, which are addresses.

Values entered in column I & AE are percentages with the columns formatted as %.
I now have both columns and all the formulas correctly displaying and adding as percent.

So I use this in the code and seems to work.

If (Range("I20") / 1) 1 Then

The Calculate code does not respond, but the code in the sheet_change does.

So, I guess problem solved.

Howard



Howard