Solved

Modify Macro to run if Cell D19 is changed

Posted on 2014-07-31
451 Views
Hi Experts,

Need your help to modify this macro to re-run when the value in D19:
1. Changes from 0 to any other number
2. Changes from and any number to another number greater than 0,
3. DOES NOT RUN when the value changes from any other number to 0.

Is that possible?

``````'Dim boolchange As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Dim dblAnnual As Double

If boolChange Then Exit Sub
boolChange = False
dblAnnual = Range("D19").Value
Case "\$D\$23:\$F\$23" 'Frequency - but happens if value is deleted
boolChange = True
Range("D24").Value = "Please Select Input Method"
Range("D25").Value = "Please Select Input Method"
Range("D26").Value = "Please Select Input Method"
Range("D27").Value = "Please Select Input Method"
Case "\$D\$23" 'Frequency - changing this resets all values to defaults
boolChange = True
Select Case Target.Value
'Case ""
'    Range("D24").Value = "Waiting for Frequency Selection"
'    Range("D25").Value = "Waiting for Frequency Selection"
'    Range("D26").Value = dblAnnual
Case "Manual Asset"
Range("D24").Value = "Continue to Manual Section Below"
Range("D25").Value = "Continue to Manual Section Below"
Range("D26").Value = "Continue to Manual Section Below"
Range("D27").Value = "Continue to Manual Section Below"
Case "Daily Sorties"
Range("D24").Value = Round(dblAnnual / 365, 0)
Range("D25").Value = Round(dblAnnual / 12, 0)
Range("D26").Value = Round(dblAnnual / 365, 0) * 365
Range("D27").Value = Round(Range("D21").Value * Range("D26"), 0)
Case "Monthly Sorties"
Range("D24").Value = Round(dblAnnual / 365, 0)
Range("D25").Value = Round(dblAnnual / 12, 0)
Range("D26").Value = Round(dblAnnual / 12, 0) * 12
Range("D27").Value = Round(Range("D21").Value * Range("D26"), 0)
Case "Annual Sorties"
Range("D24").Value = Round(dblAnnual / 365, 0)
Range("D25").Value = Round(dblAnnual / 12, 0)
Range("D26").Value = dblAnnual
Range("D27").Value = Round(Range("D21").Value * Range("D26"), 0)
Case "Flight Hours"
Range("D24").Value = Round(dblAnnual / 365, 0)
Range("D25").Value = Round(dblAnnual / 12, 0)
Range("D26").Value = dblAnnual
Range("D27").Value = Round(Range("D21").Value * Range("D26"), 0)
End Select
Case "\$D\$24" 'Daily amount
boolChange = True
Range("D23").Value = "Daily Sorties"
Range("D25").Value = Round((Target.Value * 365) / 12, 0)
Range("D26").Value = Target.Value * 365
Range("D27").Value = Round(Range("D21").Value * Range("D26"), 0)
Case "\$D\$25" 'Monthly amount
boolChange = True
Range("D23").Value = "Monthly Sorties"
Range("D25").Value = Round((Target.Value * 12) / 365, 0)
Range("D26").Value = Round((Target.Value * 12), 0)
Range("D27").Value = Round(Range("D21").Value * Range("D26"), 0)
Case "\$D\$26" 'Annual amount
boolChange = True
Range("D23").Value = "Annual Sorties"
Range("D24").Value = Round((Target.Value / 365), 0)
Range("D25").Value = Round((Target.Value / 12), 0)
Range("D27").Value = Round(Range("D21").Value * Range("D26"), 0)
Case "\$D\$27" 'Annual Flight Hours amount
boolChange = True
Range("D23").Value = "Flight Hours"
Range("D24").Value = Int(Round((Target.Value / Range("D21").Value) / 365, 0))
Range("D25").Value = Int(Round((Target.Value / Range("D21").Value) / 12, 0))
Range("D26").Value = Round((Target.Value / Range("D21")), 0)
'do anything?
End Select
boolChange = False
End Sub
``````
EE-SAFE-FrequencyMacro.xlsm
0
Question by:-Polak
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 8
• 7

LVL 22

Expert Comment

ID: 40232826
The macro runs on every change in the worksheet, and you don't want it to run if D19 <= 0, correct?
if so change line 5 to

If boolchange Or Range("D19").Value <= 0 Then Exit Sub
0

LVL 1

Author Comment

ID: 40232892
The macro runs when a change is made on the yellow cells D24:D27.

In the real-version of this workbook, D19 becomes zero when the [reset values button] is pressed at the bottom of the yellow cells.

D19 starts life = 0, then through several drop-down selections D19 can become any number >0. I would like for the macro to run each time D19 changes from 0 to any number and from any number to any other number. I do not want the macro running when D19 is reset (using the reset values button) to 0.
0

LVL 22

Expert Comment

ID: 40232937
Insert
Range("D19").Value = 0
in the reset macro, just before
boolChange = True

Then the worksheet change event macro will only run when D19 is > 0.
0

LVL 1

Author Comment

ID: 40234875
I gave it a shot, but it doesn't appear to work please see the attached.

When D19 is changed and a selection is made in D23 everything works as it did. But a subsequent change in D19 does not change the values in D24:27
EE-SAFE-FrequencyMacro.xlsm
0

LVL 22

Expert Comment

ID: 40235154
The macro runs on changes in D19.
But nothing happens, since all cases in Select Case refers to Target = the cell that is changed.
And all cases "Daily Sorties" etc. are the values of D23, and none match the value in Target D19.

But when D19 change, the Select Case should get the value or address to match from D23, just like changing D23.
See if this is what you want.
EE-SAFE-FrequencyMacro.xlsm
0

LVL 1

Author Comment

ID: 40235202
Yes sir that works! One slight issue when I tried to put it into production though. D19 in the real workbook is calculated using a SUMIF formula, specifically:

``````=IF(D15="All",SUMIFS(HisDataSum!\$A:\$A,HisDataSum!\$B:\$B,IF(D13="All","<>0",D13),HisDataSum!\$C:\$C,IF(D14="All","<>0",D14),HisDataSum!\$E:\$E,IF(D16="All","<>0",D16),HisDataSum!\$I:\$I,"<>Not Matched"), SUMIFS(HisDataSum!\$A:\$A,HisDataSum!\$B:\$B,IF(D13="All","<>0",D13),HisDataSum!\$C:\$C,IF(D14="All","<>0",D14),HisDataSum!\$D:\$D,IF(D15="All","<>0",D15),HisDataSum!\$E:\$E,IF(D16="All","<>0",D16)))
``````
When I go to manually change D19 the macro works as intended, however if I change one of the drop-down selectors in either D13:D16. D19 will re-calculate and the value changes; however, the macro does not re-run in D24:D27.....

If you need me to mock-up some menu's/drop down selectors to better explain let me know.
0

LVL 22

Expert Comment

ID: 40235255
A worksheet calculation does not trigger the worksheet change event to run.
To get that, we have to use the worksheet calculate event.

Then both events must run the macro.
EE-SAFE-FrequencyMacro.xlsm
0

LVL 1

Author Comment

ID: 40235365
Understood and sorry I didn't think the calculation would make a difference when I did the mock up.

I noticed that you removed every Case except for "Daily Amount", was that on purpose? I can't seem to get it to work again by pasting in the other cases and setting Target Value to rgCase.....
0

LVL 22

Accepted Solution

Ejgil Hedegaard earned 500 total points
ID: 40236044
I did not intentionally remove any case (sorry), but now its back from the original file.

You can't use Target for the calculation event (thus replaced by rgCase), because the second select case depend of the value in cell D23, and not the Target value.

It is a bit confusing with the 2 select cases inside each other
1 for the calculation depending on D23 value, also when other cells are changed.

I split the select cases to make it easier to understand and work with (removed rgCase).
The calculation event just run UpdateValues (if D19 >0).
Worksheet change event does what was there, Target in range D23:D27, and when D23 is Target, also run UpdateValues (if D19 >0).

Removed setting D19 = 0 in the Reset sub, because that makes no sense when D19 is a formula.
Changed G19 on the Default tab from Yes to No, for the same reason.
You have to make reset of the formula value in the input values for the formula.
EE-SAFE-FrequencyMacro-A.xlsm
0

LVL 1

Author Comment

ID: 40238982
Disregard my previous, if you read it; this work great, thank you for the troubleshooting!
0

LVL 1

Author Comment

ID: 40239347
Okay so this is weird... I put it into production (I can't attach due to sensitive information) and for some reason the macro will not stop looping....

The code, format, etc... is exactly the same as the EESafe file; however, for whatever reason when I try to manually input Daily, Monthly, Annual, or Flight Hour inputs the macro recalculates off of D19 again.....

I've even set D19 = G19 in the production version eliminating the Sumif Formula but no luck; however, if I set D19 to 0 or a negative # I can again use the Daily, Monthly, Annual, or Flight Hour cases correctly (this tells me the formulas for those cases are working fine)

Any reason you can think of why the macro would run correctly in the EESafe file, but loops continuously in the production version?
0

LVL 22

Expert Comment

ID: 40239488
There are no loops in the subs, so something must trigger the events continuously.
Can't tell without seeing what else is in the file.

Here is a version where I have replaced the boolChange variable with EnableEvents=False at the beginning and EnableEvents=True at the end of the 2 events, calculate and worksheet-change and in the Reset sub.
Then other events are disabled when the sub runs.
I prefer to use that, because then the results written to the cells do not trigger the event once more (to abort on the first line, checking boolChange).

A disadvantage is that EnableEvent is not automatically set to True when the code ends, so if you stop the code for debug and abort, then the EnableEvent must be set to True again.

It is the same with calculation set to Manual, or the Statusbar.
The status is maintained until changed.
I have a small macro in my Personal macros to reset, shortcut Ctrl+Shift+R
The macro has these 3 statements
Sub ResetStatus()
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
EE-SAFE-FrequencyMacro-B.xlsm
0

LVL 1

Author Comment

ID: 40239689
Okay that didn't solve the problem, but I understand what's going on here...

There are many other cells (about 150) that use the values in D24:D27 to calculate their respective values. When a manual entry is made in cells D24:D27 those 150 cells re-calculate. This triggers the calculation event and causes the macro to run again and use D19 to calculate D24:D27, hence the appearance of looping.

Is there anyway to set the worksheet calculation event only if the calculation occurs in Cell D19?
0

LVL 22

Expert Comment

ID: 40239846
Change the calculation event to this, then the sub will only run once, and only when D19 change.

``````Private Sub Worksheet_Calculate()
Static D19Value

If Range("D19") <= 0 Or Range("D19") = D19Value Then Exit Sub
D19Value = Range("D19")

Application.EnableEvents = False
UpdateValues
Application.EnableEvents = True

End Sub
``````

I think you have an error in the code in the change event for Case "\$D\$25" 'Monthly amount
If a value is typed in D25, the cells in the range D23:D27 are assigned new values, also D25 (the input cell), but not D24.
0

LVL 1

Author Comment

ID: 40239913
Yep, caught the D25 vs. D24 error myself a little while ago, as for the calculation event the static fixed the issue thank you!
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diâ€¦
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This lâ€¦
Suggested Courses
Course of the Month2 days, 17 hours left to enroll