Change XY maxvalues for all graphs with same name

Hi,

I have 8  histograms on my sheet, I have calculated the maximum scale from the chart data they use.

My charts are named as CHA1, CHA2, CHA3, CHA4, CHA5, CHA6, CHA7, CHA8

But I also have other charts on the page, I am aware of how to change named charts and numbered ones etc, but how do I only change all those that have CHA in the name?

Cheers
DemonForceAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

starts with the letters CHA or contains the letters CHA? Here is code that only runs for charts that start with the upper case letters CHA

Sub test()

Dim ws As Worksheet
Dim chartobj As ChartObject
Set ws = ThisWorkbook.Sheets("Sheet1")

For Each chartobj In ws.ChartObjects
    If Left(chartobj.Name, 3) = "CHA" Then
        MsgBox (chartobj.Name & " starts with CHA")
    End If
Next chartobj

End Sub

Open in new window


cheers, teylyn
0
 
byundtCommented:
If you want to set the maximum value of the Y-axis scale on the histogram charts, you can modify teylyn's code as follows:
Sub SetMaxY_Scale()
Dim chartobj As ChartObject
Dim dMax As Double
dMax = Application.InputBox("Please enter the maximum scale value for histogram charts named CHAxx", Type:=1)
With ActiveWorkbook.Worksheets("Sheet1")
    For Each chartobj In .ChartObjects
        If Left(chartobj.Name, 3) = "CHA" Then
            chartobj.Chart.Axes(xlValue).MaximumScale = dMax
        End If
    Next chartobj
End With
End Sub

Open in new window

As written, the code displays an input box asking for the maximum value. You could alternatively pass that value as a parameter to the macro  when it is called. If so, you would begin the sub as follows:
Sub SetMaxY_Scale(dMax As Double)
Dim chartobj As ChartObject
With ActiveWorkbook.Worksheets("Sheet1")

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.