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?

 

[Comfy]

Private Sub Worksheet_Calculate(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

/Comf

 

[cloughna]

When I run that I get a compile error:

"Procedure declaration does not match description of event or procedure having the same name"

At the same time as the above error message pops up, it automatically highlights "Private Sub Worksheet_Calculate(ByVal Target As Range" (does not highlight the closing parenthesis.

Thoughts? (Thanks for the speedy response by the way)

 

[mikerickson]

Excel does not pass a Target argument to the Calculate event.
If you want to modify a Change event to run whenever V19 or HN19 change (either because the user change their value or the cells feeding the formulas in them changed) you could use code like this.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim keyRange as Range
    On Error Resume Next
    Set keyRange = Application.Intersect(Target.Dependents, Range("V19,HN19"))
    On Error Goto 0
    If Target.Cells.Count = 1 Then
        If Not KeyRange Is Nothing Then
            ActiveSheet.ChartObjects("Chart 561").Chart.Axes(xlCategory) _
.MinimumScale = Target.Value
        End If
    End If
End Sub

 

[cloughna]

mikerickson,

I gave that a shot, and my chart axes don't seem to be updating. what's more... it appears that your code does not include the maximumscale option.

I've got no other suggestions, as I'm having a tough time following your code (surely more a reflection of me than your code).

 

[AlphaFrog]

This should update your chart anytime any calculation is done on the sheet. You can also trigger a calculation manually by pressing the F9 key.

Private Sub Worksheet_Calculate()

    With ActiveSheet.ChartObjects("Chart 561").Chart.Axes(xlCategory)
        .MaximumScale = Range([COLOR="Red"]"HN19"[/COLOR]).Value
        .MinimumScale = Range([COLOR="Red"]"V19"[/COLOR]).Value
    End With
    
End Sub

Note: HN19 and V19 are the two cells with the formulas that calculate the Min and Max scales.

 

[AlphaFrog]

The other way this could be done is to update the scale of the chart only when the chart's data changes and have the macro do the calculation.

For example, the chart's data is in say A1:B100 and you want to calculate the Max-scale to the max value in column B + 1 and the min-scale calculated to the min value in column B - 1. The code below only runs when you make a change in the values in B1:B100 and then it does the calculations and sets the scale.

Private Sub Worksheet_Change(ByVal Target As Range)

    'Reset scale when values in B1:B100 change
    If Not intesect(Target, Range("B1:B100")) Is Nothing Then
    
        With ActiveSheet.ChartObjects("Chart 561").Chart.Axes(xlCategory)
            .MaximumScale = Application.Max(Range("B1:B100")) + 1
            .MinimumScale = Application.Max(Range("B1:B100")) - 1
        End With

    End If

End Sub

 

[cloughna]

The other way this could be done is to update the scale of the chart only when the chart's data changes and have the macro do the calculation.

For example, the chart's data is in say A1:B100 and you want to calculate the Max-scale to the max value in column B + 1 and the min-scale calculated to the min value in column B - 1. The code below only runs when you make a change in the values in B1:B100 and then it does the calculations and sets the scale.

Private Sub Worksheet_Change(ByVal Target As Range)

    'Reset scale when values in B1:B100 change
    If Not intesect(Target, Range("B1:B100")) Is Nothing Then
    
        With ActiveSheet.ChartObjects("Chart 561").Chart.Axes(xlCategory)
            .MaximumScale = Application.Max(Range("B1:B100")) + 1
            .MinimumScale = Application.Max(Range("B1:B100")) - 1
        End With

    End If

End Sub

This would mean I would have to manually update the chart data correct? So if all the data was formulas, and only the cell value (not cell contents) updated, it would not work?

 

[AlphaFrog]

You have to have data somewhere for the formulas to calculate off of don't you?

 

[cloughna]

You have to have data somewhere for the formulas to calculate off of don't you?

Of course, but the issue I've been having with Worksheet_change is this...

lets say in cell B1 I have the value 1
in cell B2 I have =B1*2... show it shows 2.

If I want the cell B2 to be driving my scale axis (and so use it in the above cells), updating cell B1 to, say, 3 so that B2 shows 6, will not change my scale. I would have to double-click in the B2 cell to open up the formula, and then press enter (so no changes were made, but excel picks up that there could have been a change) and it regenerates.