Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

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
0
DemonForce
Asked:
DemonForce
2 Solutions
 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now