Solved

Change XY maxvalues for all graphs with same name

Posted on 2014-01-27
2
208 Views
Last Modified: 2014-02-01
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
Comment
Question by:DemonForce
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 250 total points
ID: 39813580
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
 
LVL 81

Assisted Solution

by:byundt
byundt earned 250 total points
ID: 39813770
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question