Chart Help
Posted by Aaron on December 26, 2001 9:03 AM
I am trying to get my chart to change the Y Axis automatically when the data is changed. For instance: If I have 10 periods of data for my X Axis, and my Y Axis ranges from 10 to 100, I would like for my Y Axis range to also range from 10 to 100. I really need help, and I know I didn't ask a very clear question, but if anyone could help I would greatly appreciate it.
Also if my Y Axis Data Chages from 100 to 100K I would like for my chart to change. Thanks again
Posted by Mike on December 27, 2001 8:11 AM
You could do this with a little bit of VBA. Put this code in the WorkSheet_Change subroutine:
With Worksheets("Sheet1").ChartObjects(1).Chart.Axes(xlValue)
.MaximumScale = Worksheets("Sheet1").Range("A1").Value
.MinimumScale = Worksheets("Sheet1").Range("B2").Value
End With
You first have to set up two cells that contain the max and min values that you want to set the Y axis to. You should be able to do this easily with the MAX and MIN Excel funtions. You could also do this programatically but it would take me an hour or so to figure it out. The code I posted assumes A1 contains the MAX and B1 contains the MIN. It also assumes Sheet1 and the first chart on the page. With this code, anytime a value on the worksheet changes it recalculates the Y axis.
Email me if you have any questions.
Posted by Aaron on December 27, 2001 10:32 AM
Thank you very much for your help. This is exactly what I was looking to do. However, can you let me know of the code to do two things:
1) As I change range info, run that macro automatically
2) Get the Max and Min Lines on the Y Axes to be set as well? I hope that makes since. thx for your help.
Posted by Mike on December 27, 2001 11:01 AM
If you put the code in the Worksheet_Change routine then it will automatically update the Y scale whenever you change any entry on the worksheet. If the data is on another sheet then just put the code in the routine for that sheet.
I don't understand your second question. I'm a little slow sometimes. Could you reword it?
Good luck...Mike
Posted by Aaron on December 27, 2001 11:28 AM
You are not slow, I just don't know how to ask a question.
1) On the Routine?: I actually created a module. Was that correct? If so how do I get that Module on the data page? I have three graphs on three seperate pages that key off of a data page.
2) My unclear question was: If for instance My max value is $267 I will have a $270 line above it. This is really just a cosmetic issue, but it would be nice. thx for all of your help If you put the code in the Worksheet_Change routine then it will automatically update the Y scale whenever you change any entry on the worksheet. If the data is on another sheet then just put the code in the routine for that sheet. I don't understand your second question. I'm a little slow sometimes. Could you reword it? Good luck...Mike : Thank you very much for your help. This is exactly what I was looking to do. However, can you let me know of the code to do two things:
