Solved

Change XY maxvalues for all graphs with same name

Posted on 2014-01-27
2
176 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
2 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now