CODEWITHSUNDEEP
excelvba
Ace_1918
Ace_1918

Reputation: 29

Update all series axes within a chart

I have this macro in Excel that updates the ranges of my charts based on a given value in cell G2 (min bound) and G3 (max bound).

The macro should work across multiple worksheets within an Excel file, and should update the axes on each chart within each sheet.

The charts plot two sets of data against each other, where one of the series is a secondary axis.

Sub UpdateChartRange()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim chtObj As ChartObject
    Dim minBound As Double
    Dim maxBound As Double
    Dim srs As Series
    
    ' Set the workbook object to the current workbook
    Set wb = Application.ActiveWorkbook
    
    ' Get the minimum and maximum bounds from the first worksheet
    minBound = wb.Worksheets(1).Range("G2").Value
    maxBound = wb.Worksheets(1).Range("G3").Value
    
    ' Loop through all the worksheets in the workbook
    For Each ws In wb.Worksheets
        ' Loop through all the chart objects in the worksheet
        For Each chtObj In ws.ChartObjects
            ' Loop through each data series in the chart
            For Each srs In chtObj.Chart.SeriesCollection
                ' Modify the X axis range of the chart
                With srs
                    Debug.Print "Modifying series " & srs.Name & " in chart " & chtObj.Name & " on sheet " & ws.Name
                    chtObj.Chart.Axes(xlCategory).MinimumScale = minBound
                    chtObj.Chart.Axes(xlCategory).MaximumScale = maxBound
                End With
            Next srs
        Next chtObj
    Next ws
    
    ' Notify the user that the chart range has been updated
    MsgBox "Chart range updated successfully!"
End Sub

Chart with two sets of Data
enter image description here

The code updates the x-axis range for the blue line series but not the orange one. I want both to be updated simultaneously.

Upvotes: 0

Views: 44

Answers (1)

kevin
kevin

Reputation: 2157

From using the macro recorder, when updating the secondary axis, a second parameter is specified: chtObj.Chart.Axes(xlValue, xlSecondary)

There are only 2 axes, so you don't need to loop through each series, you can just directly change each axis.

' Loop through all the worksheets in the workbook
For Each ws In wb.Worksheets
    ' Loop through all the chart objects in the worksheet
    For Each chtObj In ws.ChartObjects

                Debug.Print "Modifying chart " & chtObj.Name & " on sheet " & ws.Name
                chtObj.Chart.Axes(xlCategory).MinimumScale = minBound
                chtObj.Chart.Axes(xlCategory).MaximumScale = maxBound
                chtObj.Chart.Axes(xlCategory, xlSecondary).MinimumScale = minBound
                chtObj.Chart.Axes(xlCategory, xlSecondary).MaximumScale = maxBound

    Next chtObj
Next ws

Upvotes: 0

Related Questions