Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best practice sheet Change/Calculate?
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%? Thanks. Howard Here are the two codes that are working for me now. Private Sub Worksheet_Change(ByVal Target As Range) If Range("I20") 100 Then MsgBox "Funds allocated among properties cannot be greater than 100%" & vbCr & _ "Review values in Columns I and AE" End If If Intersect(Target, Range("B4:R18")) Is Nothing Or Target.Cells.Count 1 Then Exit Sub If InStr(1, Target, ",") 0 Or InStr(1, Target, ";") 0 Then Application.EnableEvents = False With Target .Replace What:=",", Replacement:="", LookAt:=xlPart .Replace What:=";", Replacement:="", LookAt:=xlPart End With Application.EnableEvents = True Else Application.EnableEvents = True Exit Sub End If MsgBox "Comma's or Semi-Colon's removed from " & Target.Address(False, False) End Sub Private Sub Worksheet_Calculate() If Range("I20").Value 100 Then MsgBox "Funds allocated among properties cannot be greater than 100%" & vbCr & _ "Review values in Columns I and AE" End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best practice sheet Change/Calculate?
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. -- Windows10 Office 2016 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best practice sheet Change/Calculate?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best practice sheet Change/Calculate?
Hi Howard,
Am Sat, 8 Jul 2017 09:06:48 -0700 (PDT) schrieb L. Howard: 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. try: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub Application.ScreenUpdating = False If Not Intersect(Target, Range("B4:D18")) Is Nothing Then If InStr(Target, ",") + InStr(Target, ";") 0 Then Target = Replace(Replace(Target, ",", ""), ";", "") End If ElseIf Not Intersect(Target, Range("I4:I18,AE4:AE18")) Is Nothing Then If Range("I20") 1 Then MsgBox "Funds allocated among properties cannot be greater than 100%" & vbCr & _ "Review values in Columns I and AE" End If End If Application.ScreenUpdating = True End Sub Regards Claus B. -- Windows10 Office 2016 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best practice sheet Change/Calculate?
Perhaps...
Use a Select Case construct on Target.Address vs. an If...Then construct. Use CF to 'flag' offending cells to visually draw user attention to them. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best practice sheet Change/Calculate?
On Saturday, July 8, 2017 at 9:39:06 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Sat, 8 Jul 2017 09:06:48 -0700 (PDT) schrieb L. Howard: 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. try: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub Application.ScreenUpdating = False If Not Intersect(Target, Range("B4:D18")) Is Nothing Then If InStr(Target, ",") + InStr(Target, ";") 0 Then Target = Replace(Replace(Target, ",", ""), ";", "") End If ElseIf Not Intersect(Target, Range("I4:I18,AE4:AE18")) Is Nothing Then If Range("I20") 1 Then MsgBox "Funds allocated among properties cannot be greater than 100%" & vbCr & _ "Review values in Columns I and AE" End If End If Application.ScreenUpdating = True End Sub Regards Claus B. -- Windows10 Office 2016 Hi Claus, Garry, I am using this which covers the bases on two different ranges for the commas/semi-colons and alerts when I20 is 100. Private Sub Worksheet_Change(ByVal Target As Range) If (Range("I20") / 1) 1 Then MsgBox "Funds allocated among properties cannot be greater than 100%" & vbCr & _ "Review values in Columns I and AE" End If If Intersect(Target, Range("B4:D18,Z4:AB18")) Is Nothing Or Target.Cells.Count 1 Then Exit Sub If InStr(1, Target, ",") 0 Or InStr(1, Target, ";") 0 Then Application.EnableEvents = False With Target .Replace What:=",", Replacement:="", LookAt:=xlPart .Replace What:=";", Replacement:="", LookAt:=xlPart End With Application.EnableEvents = True Else Application.EnableEvents = True Exit Sub End If MsgBox "Comma's or Semi-Colon's removed from " & Target.Address(False, False) End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best practice sheet Change/Calculate?
My point is...
You have 2 tasks: catch 100; remove commas/semi-colons. Let CF handle the 1st; code manage the 2nd. The user should be aware of how to use the project correctly, but CFing the offending cells in one color, the target cell another is very effective and intuitive, ..IMO! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best practice sheet Change/Calculate?
My point is...
You have 2 tasks: catch 100; remove commas/semi-colons. Let CF handle the 1st; code manage the 2nd. The user should be aware of how to use the project correctly, but CFing the offending cells in one color, the target cell another is very effective and intuitive, ..IMO! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Protection Best Practice: AKA: Real Sheet Protection | Excel Discussion (Misc queries) | |||
Sheet change or sheet calculate? | Excel Programming | |||
Activate Sheet Best Practice (Y/N?) when xFer data between sheets? | Excel Programming | |||
help panel won't go away after saving a practice sheet | New Users to Excel | |||
Excel practice sheet. Can't view instructions. | Excel Discussion (Misc queries) |