Worksheet change vs worksheet calculate

[cloughna]

I've got some code that is driving an axis scale based on a few cells. What I would like to do is have the axis be driven by calculated cells instead. I'm thinking I need to change the code below to worksheet_calculate, but (if you haven't guessed already), I'm not sure of the changes that need be made.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$HN$19"
ActiveSheet.ChartObjects("Chart 561").Chart.Axes(xlCategory) _
.MaximumScale = Target.Value
Case "$V$19"
ActiveSheet.ChartObjects("Chart 561").Chart.Axes(xlCategory) _
.MinimumScale = Target.Value
Case Else
End Select
End Sub

Help?

 

[AlphaFrog]

The point I was making with the Worksheet_Change in post #7 is that you could get rid of the formula in B2. You could have the Worksheet_Change macro do the B2 calculation and update the scale whenever you make a change to B1.

The Worksheet_Change macro both Calculates and Updates the scales when the raw data changes.

 

[cloughna]

The point I was making with the Worksheet_Change in post #7 is that you could get rid of the formula in B2. You could have the Worksheet_Change macro do the B2 calculation and update the scale whenever you make a change to B1.

The Worksheet_Change macro both Calculates and Updates the scales when the raw data changes.

My apologies, I get what you are saying now.

Unfortunately, my inkling is that it would be more complicated. The cell that has the limit I want has this array formula...

{=$I$15+SQRT(SUM(($L$10:$L$14/IF(($M$10:$M$14)="",$I$17*3,($M$10:$M$14*3)))^2))*V18}

For your reference, the values are

I15 = .02 - this is the mean
L10 = 0.0080
L11 = 0.0080
L12 = 0.0040
L13 = 0.0050
L14 = 0.0040
M10 thru M14 are currently blanks
I17 = 1
v18 = 6 (this is how many standard deviations I am using to determine the value)

Ultimately it is feeding into a normal distribution and I want the scale limits to be at 6 standard deviations from the mean.

Essentially the equation is saying take the mean, then calculate the standard deviation, then multiply that by how many standard deviation I want (V18)

 

[AlphaFrog]

So lets say your raw data is in L10:L14
The SDs you want to calculate for is in V18.
I'm not sure what M10:M14 does but that doesn't matter

This will update the chart scale from the array formulas in B2 and B3 whenever there is a change in any cell in
L10:L14, M10:M14, or V18

Lets call these cells the "Raw" data. The "Raw" data are any cells used in your array formulas to calculate the scale.

Then the Worksheet_Change macro runs when there is any change in the Raw data and uses the calculations from your array formulas to set the scale.

The order of events.

• Make change to the raw data
• Array formulas recalculate
• Worksheet_Change macro is triggered
  • Macro checks if change occured in Raw data cells
  • If yes, Use array formulas results to update the scale.

Private Sub Worksheet_Change(ByVal Target As Range)

    'Check if "Raw" data changed
    If Not Intersect(Target, Range("L10:L14, M10:M14, V18")) Is Nothing Then
    
        With ActiveSheet.ChartObjects("Chart 561").Chart.Axes(xlCategory)
            .MaximumScale = Range("B2")
            .MinimumScale = Range("B3")
        End With
    End If

End Sub

 

[cloughna]

So lets say your raw data is in L10:L14
The SDs you want to calculate for is in V18.
I'm not sure what M10:M14 does but that doesn't matter

This will update the chart scale from the array formulas in B2 and B3 whenever there is a change in any cell in
L10:L14, M10:M14, or V18

Lets call these cells the "Raw" data. The "Raw" data are any cells used in your array formulas to calculate the scale.

Then the Worksheet_Change macro runs when there is any change in the Raw data and uses the calculations from your array formulas to set the scale.

The order of events.

• Make change to the raw data
• Array formulas recalculate
• Worksheet_Change macro is triggered
  • Macro checks if change occured in Raw data cells
  • If yes, Use array formulas results to update the scale.

Private Sub Worksheet_Change(ByVal Target As Range)

    'Check if "Raw" data changed
    If Not Intersect(Target, Range("L10:L14, M10:M14, V18")) Is Nothing Then
    
        With ActiveSheet.ChartObjects("Chart 561").Chart.Axes(xlCategory)
            .MaximumScale = Range("B2")
            .MinimumScale = Range("B3")
        End With
    End If

End Sub

Money.

Thanks so much man, works like a charm.

 

[AlphaFrog]

Whew!