2

Have been using this blog to link chart axis to cell values.

Sub ScaleAxes() Dim wks As Worksheet Set ws = Worksheets("AXIS") Set cht = ActiveWorkbook.ChartObjects("ChartName1","ChartName2") For Each cht In ActiveWorkbook.ChartObjects cht.Activate With ActiveChart.Axes(xlCategory, xlPrimary) .MaximumScale = ws.Range("$B$12").Value       .MinimumScale = ws.Range("$B$11").Value      .MajorUnit = ws.Range("$B$13").Value End With Next cht End Sub

I'm aiming for the values a single worksheet with axis values to update multiple charts on different worksheets. Most examples are using charts on the same worksheet. I currently get error 438 - any ideas?

Improve this question

1 Answer 1

Reset to default
0

Try the code below, explanations inside the code as comments:

Option Explicit Sub ScaleAxes() Dim Sht As Worksheet Dim ws As Worksheet Dim chtObj As ChartObject Dim ChtNames Set ws = Worksheets("AXIS") ' you need to get the names of the charts into an array, not ChartObjects array ChtNames = Array("ChartName1", "ChartName2") ' first loop through all worksheet For Each Sht In ActiveWorkbook.Worksheets ' loop through all ChartObjects in each worksheet For Each chtObj In Sht.ChartObjects With chtObj '=== use the Match function to check if current chart's name is found within the ChtNames array === If Not IsError(Application.Match(.Name, ChtNames, 0)) Then With .Chart.Axes(xlCategory, xlPrimary) .MaximumScale = ws.Range("B12").Value .MinimumScale = ws.Range("B11").Value .MajorUnit = ws.Range("B13").Value End With End If End With Next chtObj Next Sht End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Would I need to change chart objects if it is a chart sheet?
@tj123 you didn't mention anythins about chart sheets in your post, you wrote "multiple charts on different worksheets" which is different

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.