Worksheet_Change to trigger a graph scale change

[grayco4]

Greetings EXCELent users,

I have a dynamic graph that does not auto scale on the lower end of the vertical axis.

I have the following code that does re-scale the graph according to values in the worksheet cells.


This code selects the graph and runs "ScaleAxes"
Sub RUNNIT()
'
' RUNNIT Macro
'
' Keyboard Shortcut: Ctrl+r
'
ActiveSheet.Shapes.Range(Array("Group 11")).Select
ActiveSheet.ChartObjects("Chart 12").Activate
Application.Run "'Graph scale test.xlsm'!ScaleAxes"
Range("A15").Select
End Sub


This actually changes the graphs axis.

Sub ScaleAxes()
With ActiveChart.Axes(xlValue, xlPrimary)
.MaximumScale = ActiveSheet.Range("$AM$1").Value
.MinimumScale = ActiveSheet.Range("$AM$2").Value

End With
End Sub

I would like to make one macro that rescales the axis and is triggered by a change in cell A1.

I have been experimenting with this

Private Sub Worksheet_Change(ByVal Target As Range)
but so far I have not found a way to actually trigger the actions of "ScaleAxes"

I would appreciate any help in combining these elements into a single VBA code that will change the graph's scaling when a value in A1 changes.

Thank you for you inputs.
Glen

[grayco4]

I found it! When you are changing the value of a cell using an ActiveX slidebar, it is the same as re-calculating the worksheet. This code worked

Private Sub Worksheet_Calculate()

ActiveSheet.Shapes.Range(Array("Group 11")).Select
ActiveSheet.ChartObjects("Chart 12").Activate

Application.Run "'Graph scale test.xlsm'!ScaleAxes"

Range("A15").Select
End Sub

[grayco4]

I found it! When you are changing the value of a cell using an ActiveX slidebar, it is the same as re-calculating the worksheet. This code worked

Private Sub Worksheet_Calculate()

ActiveSheet.Shapes.Range(Array("Group 11")).Select
ActiveSheet.ChartObjects("Chart 12").Activate

Application.Run "'Graph scale test.xlsm'!ScaleAxes"

Range("A15").Select
End Sub

It works but how can I make it only run when I am on the worksheet that has the graphs? Or how can I change the code to specify the exact worksheet instead of "ActiveSheet"?
Everytime I open the file, save the file or make any change, I get an error unless I'm on the graph page.

[Aflatoon]

It looks like you could just replace all your code with:

Private Sub Worksheet_Calculate()
    With Me.ChartObjects("Chart 12").Chart.Axes(xlValue, xlPrimary)
        .MaximumScale = Range("$AM$1").Value
        .MinimumScale = Range("$AM$2").Value
    End With
End Sub

[grayco4]

It looks like you could just replace all your code with:

Private Sub Worksheet_Calculate()
    With Me.ChartObjects("Chart 12").Chart.Axes(xlValue, xlPrimary)
        .MaximumScale = Range("$AM$1").Value
        .MinimumScale = Range("$AM$2").Value
    End With
End Sub

Thanks for your reply. I came up with something similar.

Private Sub Worksheet_Calculate()
Sheets("Raw Data Graphs").Shapes.Range(Array("Group 11")).Select
Sheets("Raw Data Graphs").ChartObjects("Chart 12").Activate
With ActiveChart.Axes(xlValue, xlPrimary)
.MaximumScale = Sheets("Raw Data Graphs").Range("$AM$1").Value
.MinimumScale = Sheets("Raw Data Graphs").Range("$AM$2").Value
End With

ActiveCell.Select
End Sub


This works but whenever the sheet recalculates the worksheet changes to the "Raw Data Graphs" worksheet. Is there a way to de-activate the chart without selecting or activating something else?

Thanks for your input.
Glen

[grayco4]

I just changed the code to this;
Option Explicit
Private Sub Worksheet_Calculate()

Sheets("Raw Data Graphs").ChartObjects("Chart 12").Activate
With ActiveChart.Axes(xlValue, xlPrimary)
.MaximumScale = Sheets("Raw Data Graphs").Range("$AM$1").Value
.MinimumScale = Sheets("Raw Data Graphs").Range("$AM$2").Value
End With


End Sub

I thought it was the last statement that was sending it to the Raw Data Graphs worksheet. It still goes there. Is there a way to stop it?

In your code could you explain the "With Me.ChartObjects..." What is the "Me"?

Thanks

[grayco4]

It looks like you could just replace all your code with:

Private Sub Worksheet_Calculate()
    With Me.ChartObjects("Chart 12").Chart.Axes(xlValue, xlPrimary)
        .MaximumScale = Range("$AM$1").Value
        .MinimumScale = Range("$AM$2").Value
    End With
End Sub

I'm a bit embarrassed that I didn't try your code before I tried to fix mine. Your code fixed all the issues.
Thank you very much. The best way to deactivate a chart is to not activate it! Who'ld of thought! Sheesh!
I way over think things sometimes.

Really, Thank you for y our help!

[Aflatoon]

You are welcome!