Autoscale chart X & Y axes dependent on table values & re-run macro after changing input

Stretch8027

New Member
Joined
Nov 30, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
I have a chart embedded in a worksheet plotting 2 parameters. I need to do 2 things.

1. I want to use VBA to dynamically scale the axes whenever the data table updates.

The input table is in sheet1
The chart is in sheet 2

Code similar to that below would perform the function required, but i'm unsure how to define the chart object as it is inserted as an embedded object in a different worksheet. There is only one chart in the workbook. Not sure is i have to define variables or not

Sub ScaleAxes()
With ActiveChart.Axes(xlCategory, xlPrimary)
.MinimumScale = Sheet1.Range("P8").Value
.MaximumScale = Sheet1.Range("P9").Value
.MajorUnit = Sheet1.Range("P10").Value
End With
With ActiveChart.Axes(xlValue, xlPrimary)
.MinimumScale = Sheet1.Range("q8").Value
.MaximumScale = Sheet1.Range("q9").Value
.MajorUnit = Sheet1.Range("q10").Value
End With
End Sub

2. Insert code to re-run Macro after changing any values in the range C7:C17 on Sheet1 ("Input")

I hope I have managed to do a reasonable job of explaining my problem - VBA newbie

Thanks in advance
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
Hi Stretch, welcome to MrExcel

Next time you post VBA code, please use code tags around your code: click on the little VBA icon at the top of the post window and insert your code inbetween (see my tagline below my post here)

as to your second question, see the first part of my reply in this thread 2 inputs consolidated live as to how to achieve the automatic update after a change to the sheet.

As to the code you will need in the sheet module (that is for the sheet where the data resides):
VBA Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("C7:C17")) Is Nothing Then
        'cell changed is in chart data
        'call the axis update macro
       ScaleAxes
    End If
End Sub

THen you need to modify the ScaleAxes code. It will search for the correct chart on the sheet (see the comments with <<<< ) and act on that one
VBA Code:
Sub ScaleAxes()
    Dim chChart As ChartObject
    
    For Each chChart In Sheets("Sheet2").ChartObjects   '<<<< modify sheet name where chart sits
        If chChart.Name = "YourChartName" Then          '<<<< modify chart name
            With chChart.Chart.Axes(xlCategory, xlPrimary)
                .MinimumScale = Sheet1.Range("P8").Value
                .MaximumScale = Sheet1.Range("P9").Value
                .MajorUnit = Sheet1.Range("P10").Value
            End With
            With chChart.Chart.Axes(xlValue, xlPrimary)
                .MinimumScale = Sheet1.Range("q8").Value
                .MaximumScale = Sheet1.Range("q9").Value
                .MajorUnit = Sheet1.Range("q10").Value
            End With
        End If
    Next chChart
End Sub
 

Stretch8027

New Member
Joined
Nov 30, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Sijpie

Many thanks for the response and advise om posting

I have tried the code but unfortunately I get the error "Subscript out of range (Error 9)"

I found a workaround the other day using the record macro feature but its not very slick as i have to click a button to recalibrate the axes. It doesn't help me improve my VBA skills so will try to debug the error myself before coming back to you.

Regards
David
 

Stretch8027

New Member
Joined
Nov 30, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi Sijpie

I have solved the subscript out of range error. I originally set the name of "Sheet3" to "KT Chart". When i change the name to "KT Chart" in the VBA Excel object the code runs without error.

Now i get a Compile: Sub or function not defined error for the automatic rescale axes on Sheet1 [ScaleAxes]. Any thoughts?
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
Where is your ScaleAxis sub ? It should be in a normal module.
 

Stretch8027

New Member
Joined
Nov 30, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Bingo - it works

Out of interest, why can't i add the ScaleAxes code to the Excel Object sheet?
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
If you put a sub into the object module for a sheet, then you can only call it from within that module. Not strictly true, you can call it using Sheet1.mysub . But for good coding practice: in the sheet or workbook modules, only store those subs that are strictly to be used by that particular object.

This ScaleAxis sub is a sub that could be used by any sheet object that wants to scale its axis, so you keep it in a general module.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,204
Messages
5,623,360
Members
415,969
Latest member
Rey99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top