Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Change XY maxvalues for all graphs with same name

Posted on 2014-01-27
Medium Priority
213 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
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

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1000 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
``````

cheers, teylyn
0

LVL 81

Assisted Solution

byundt earned 1000 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
``````
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")
``````
0

## Featured Post

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month8 days, 4 hours left to enroll

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

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