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

[Stretch8027]

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

 

[sijpie]

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):

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

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]

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

 

[sijpie]

At which line do you get the error?

 

[Stretch8027]

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]

Where is your ScaleAxis sub ? It should be in a normal module.

 

[Stretch8027]

Bingo - it works

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

 

[sijpie]

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.