PDA

View Full Version : [SOLVED] Changing the properties of many charts



ronjon65
07-10-2018, 06:06 PM
I am trying to set scale for many charts..."Chart 1", "Chart 2",... I tried some code that would operate on all the charts, but I couldn't get it to work. It needs to work for 2003+. Ideally, it would be best not to activate the chart since I *think* it sometimes causes the chart to flash in some Excel versions (even with screenupdating turned to false). For now, I just have the following code for each chart. It works, but it doesn't seem very efficient and is slow on some computers when there are a lot of charts. Any ideas on how to make it faster and avoid the flashing?



ActiveSheet.ChartObjects("Chart1").Activate

With ActiveChart.Axes(xlCategory)
.MinimumScale = Cells(13, 5)
.MaximumScale = (12, 5)
.Crosses = xlCustom
.CrossesAt = (13, 5)
End With

With ActiveChart.Axes(xlValue)
.MinimumScale = (18, 1)
.MaximumScale = (17, 1)
.Crosses = xlCustom
.CrossesAt = (18, 1)
End With

mana
07-11-2018, 04:16 AM
Option Explicit


Sub test()
Dim cho As ChartObject
Dim xMax As Double, xMIn As Double
Dim yMax As Double, yMIn As Double

xMax = Cells(12, 5).Value
xMIn = Cells(13, 5).Value
yMax = Cells(17, 1).Value
yMIn = Cells(18, 1).Value

Application.ScreenUpdating = False

For Each cho In ActiveSheet.ChartObjects
With cho.Chart
With .Axes(xlCategory)
.MinimumScale = xMIn
.MaximumScale = xMax
.Crosses = xlMinimum
End With

With .Axes(xlValue)
.MinimumScale = yMIn
.MaximumScale = yMax
.Crosses = xlMinimum
End With
End With
Next

End Sub

ronjon65
07-11-2018, 10:27 AM
Thanks! This works better than my prior code. A couple questions:

1. I realized that something in my charts is tripping the code up and it causes an error on first ".MinimumScale" line. I think is because sometimes the charts can be empty and not have data. Anyway, I put a "On Error Resume Next" and it seems to work fine. Any issues with this?

2. The code works very fast on Excel 2003 (split second). But on 2010 and 2016 it is rather slow (about 3 seconds). Of course the hardware is different and that may be the only reason. But is there something in later version of Excel that may be slowing it down? I still like 2003 since it seems more streamlined and fast.