View Full Version : Worksheet_Change to trigger a graph scale change
grayco4
10-21-2015, 01:00 PM
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
10-21-2015, 02:17 PM
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
10-21-2015, 04:10 PM
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
10-22-2015, 05:08 AM
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
10-22-2015, 08:27 AM
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
10-22-2015, 09:06 AM
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
10-22-2015, 10:28 AM
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
10-23-2015, 03:25 AM
You are welcome!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.