• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Chart Scaling

GB

Member
Hi, I am having trouble with my chart line scaling correctly. I have 2 variables to control what want to see on my chart...

1. "Start From # Rows Down" - which controls my starting point (ie my row I want to start from)
2. "Data Points" - which controls how many rows of data I want to display.

The chart scaling is set to automatic. However it looks as I want to when you open the file attached eg the chart is set to the min and max values +/- a small amount. Now if you change the either of the 2 values in the highlighted cells to a greater value, the scaling makes the min value go to zero an dthe max value go much higher, but I don't know why or how to prevent this as I want the scaling to be dynamic to the min/max values being displayed.

Can you offer a good solution?

regards
GB
 

Attachments

  • Cht.xlsx
    128.7 KB · Views: 5
Just a tweak of Somendra's macro to reduce selections and screen flicker.
For your information, "If Ymin is less than 5/6 of the Ymax, the automatic minimum Y axis scale value is zero". This is from:
http://peltiertech.com/WordPress/how-excel-calculates-automatic-chart-axis-limits/

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D2:E2")) Is Nothing Then
  With ActiveSheet.ChartObjects("Chart 1").Chart
    .Axes(xlValue).MinimumScale = Range("E8").Value
    .Axes(xlValue).MaximumScale = Range("E9").Value
    .Axes(xlCategory).MinimumScale = Range("D8").Value
    .Axes(xlCategory).MaximumScale = Range("D9").Value
  End With
End If
End Sub
 
Hi Somendra & p45cal
thanks for your help. Yes this will resolve my charting issue.
regards
GB
 
Hi, the following code will scale my chart (chart 6) no problem. Now I have two charts on the same worksheet (chart 6 and chart 18) so I want to integrate the commented section of code (the bottom section of code) into one worksheet module. Can you help please?

regards
GB

[Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B14:B13")) Is Nothing Then
With ActiveSheet.ChartObjects("Chart 6").Chart
.Axes(xlValue).MinimumScale = Range("H5").Value
.Axes(xlValue).MaximumScale = Range("H6").Value
.Axes(xlCategory).MinimumScale = Range("G5").Value
.Axes(xlCategory).MaximumScale = Range("G6").Value
End With
End If
End Sub

'Private Sub Worksheet_Change(ByVal Target As Range)
'If Not Intersect(Target, Range("B14:B13")) Is Nothing Then
'With ActiveSheet.ChartObjects("Chart 18").Chart
' .Axes(xlValue).MinimumScale = Range("G8").Value
' .Axes(xlValue).MaximumScale = Range("G9").Value
' .Axes(xlCategory).MinimumScale = Range("G5").Value
' .Axes(xlCategory).MaximumScale = Range("G6").Value]
 
try:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B14:B13")) Is Nothing Then
  With ChartObjects("Chart 6").Chart
    .Axes(xlValue).MinimumScale = Range("H5").Value
    .Axes(xlValue).MaximumScale = Range("H6").Value
    .Axes(xlCategory).MinimumScale = Range("G5").Value
    .Axes(xlCategory).MaximumScale = Range("G6").Value
  End With
  With ChartObjects("Chart 18").Chart
    .Axes(xlValue).MinimumScale = Range("G8").Value
    .Axes(xlValue).MaximumScale = Range("G9").Value
    .Axes(xlCategory).MinimumScale = Range("G5").Value
    .Axes(xlCategory).MaximumScale = Range("G6").Value
  End With
End If
End Sub
 
Thanks p45cal, I knew it would be simple (if you know VBA). Appreciate your help.

regards
GB
 
Back
Top