Solved

Modify Macro to run if Cell D19 is changed

Posted on 2014-07-31
15
428 Views
Last Modified: 2014-08-04
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
    Select Case Target.Address
        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

Open in new window

EE-SAFE-FrequencyMacro.xlsm
0
Comment
Question by:-Polak
  • 8
  • 7
15 Comments
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
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

by:-Polak
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 21

Expert Comment

by:Ejgil Hedegaard
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

by:-Polak
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 21

Expert Comment

by:Ejgil Hedegaard
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

by:-Polak
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)))

Open in new window

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 21

Expert Comment

by:Ejgil Hedegaard
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:-Polak
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 21

Accepted Solution

by:
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 address change
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

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

Author Comment

by:-Polak
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 21

Expert Comment

by:Ejgil Hedegaard
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

by:-Polak
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 21

Expert Comment

by:Ejgil Hedegaard
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

Open in new window


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

by:-Polak
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now