Solved

Excel 2007 - How to specify chart min in VBA

Posted on 2011-02-11
5
209 Views
Last Modified: 2012-05-11
I have an Excel page with two scatter graphs and I need to specify the vertical axis min to equal a value calculated in a sheet cell.

I am having trouble specifying the component and property to change.  I am a beginner in VBA.  I can get to the code page for the sheet that contains the graphs but I can find to reference to the graphs or their properties.  In some online material it mentions a column on the right which I think contains the info I want, but I have no such column when looking at the code page.

I could use help in two ways:  1) Tell me the code to write to specify the minimum for each of two graphs on one page and 2) Help me navigate the UI to find graphs and properties myself.

Thanks,
John Fistere
0
Comment
Question by:John Fistere
[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
  • 3
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34876987
Something liek this to iterate through each chart on the first worksheet

I suggest you use the VBE when the code runs to see the chart (chr1) object, use VBA's intellisense to use the code options etc

Cheers

Dave
Sub GraphAxis()
Dim ws As Worksheet
Dim chr1 As ChartObject
Set ws = Sheets(1)
For Each chr1 In ws.ChartObjects
   chr1.Chart.Axes(xlValue).MinimumScale = 1.5
Next
End Sub

Open in new window

0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34877104
to set the x-axis value min to the cell value in A1

chr1.Chart.Axes(xlValue).MinimumScale = ws.[a1]

Open in new window

0
 
LVL 2

Author Comment

by:John Fistere
ID: 34877297
Thank you for your comments.  My actual need is to set "Graph 3" on the sheet to one value and "Graph 6" to another value.  I'm getting close to doing that.  Also, in your example, it appears necessary to set ws to the value of the current sheet.  Isn't there a way to make the sub with the current sheet implicitly, without having to specify the sheet.

The overall situation is that I have twelve sheets, one for each month and the Graph 3 minimum is in Cell D36, and the Graph 6 minimum is in D37.  I also have a sheet for the year with its minimums in the same locations.

Thanks,
John
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 500 total points
ID: 34877303
1) sure
Set ws = ActiveSheet

2)

If you know the chart names you can code it directly, ie

Dave
Sub GraphAxis()
ActiveSheet.ChartObjects("Chart 3").Chart.Axes(xlValue).MinimumScale = [d36].Value
ActiveSheet.ChartObjects("Chart 6").Chart.Axes(xlValue).MinimumScale = [d37].Value
End Sub

Open in new window

0
 
LVL 2

Author Closing Comment

by:John Fistere
ID: 34885385
Thanks, Dave

I couldn't get "Sub GraphAxis() to work as a header line, but this format worked:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.ChartObjects("Chart 6").Chart.Axes(xlValue).MaximumScale = [c44].Value
ActiveSheet.ChartObjects("Chart 6").Chart.Axes(xlValue).MinimumScale = [c45].Value
End Sub

I've found I can do a better job of scaling than Excel, so I'm going to do both max and min for each graph.

I want to control other features of the graph, for example, the Title, but I have not been able to access a list of properties of graph that can be changed.  Does that have a quick and easy answer, or should I ask a new question?

Thanks and cheers,
John
0

Featured Post

Independent Software Vendors: 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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

726 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