Consulting

Results 1 to 3 of 3

Thread: Changing the properties of many charts

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Changing the properties of many charts

    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

  2. #2
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    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

  3. #3
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •