?
Solved

Modify Macro to run if Cell D19 is changed

Posted on 2014-07-31
15
Medium Priority
?
453 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
[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
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 23

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 23

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 23

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 23

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
 
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 23

Accepted Solution

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

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 23

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 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