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

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

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

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

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

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

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

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