ChartObject/Chart: Change .MajorUnit at Runtime - Weird Behaviour

[idbr]

Hi,

I have a workbook I'm using to produce a set of reports. Each report has various sheets each with various charts.

I have a combo box, which allows me to select criteria for the report. This applies filters to hidden sheets, which makes my 'new' report. The new report is then saved to a new file.

I'm trying to clean up the charts as part of the process, by ensuring that the value axis is scaled in whole numbers only.

If I run the code as an external proc call within the Change event for the combo box, it doesn't work. (Note there is other stuff happening in the change event too, whereabouts in the action order I place the call has no effect).

If I attempt to just run the code itself within the change event, it runs once ok, then on the second run generates a "method Axes of object _Chart failed" error, and Excel then crashes if I try to make any action within the VBE (memory leak maybe?).

If I run it as a standalone proc after my Change event has cleared, it works just fine.

I have to run the operation off the combobox change event (or consecutively and seamlessly, without action from the end user).

Here's the code:

Dim s As Integer
Dim c As Integer
Dim chChart As Chart
Dim choChObj As ChartObject
Dim shtCurrent As Worksheet

    For s = 1 To ActiveWorkbook.Worksheets.Count
    
        Set shtCurrent = shtCurrent
        
        Debug.Print shtCurrent.Name

        shtCurrent.Activate

        If shtCurrent.Visible = True And shtCurrent.ChartObjects.Count > 0 Then
            
            '---------------------------------------------------------------
            ' check the charts in the sheet, if major unit < 1 then set to 1
            '---------------------------------------------------------------
            
            ' loop through the ChartObjects on the sheet
            For c = 1 To shtCurrent.ChartObjects.Count

                ' activate the ChartObject
                'shtcurrent.ChartObjects(c).Activate

                Debug.Print shtCurrent.ChartObjects(c).Chart.Name
                
                ' assign variables
                Set choChObj = shtCurrent.ChartObjects(c)
                Set chChart = choChObj.Chart

                ' reset the scales back to auto
                chChart.Axes(xlValue).MajorUnitIsAuto = True

                ' is there now a decimal point in the MajorUnit?
                If InStr(1, CStr(chChart.Axes(xlValue).MajorUnit), ".") <> 0 Then

                    'if so, reset the MajorUnit to the nearest integer
                    chChart.Axes(xlValue).MajorUnit = Application.WorksheetFunction.RoundUp(chChart.Axes(xlValue).MajorUnit, 0)

                End If

            Next c

        End If

    Next s

    Worksheets("Tier 1 Dashboard").Select
    
Set chChart = Nothing
Set choChObj = Nothing
Set shtCurrent = Nothing

Any suggestions?

NB: Values per chart can range from 0 to 200, so it isn't possible to just set the MajorUnit to 1 for all circumstances.

Regards, Iain

 

[SkipVought]

Hi,

How about this...

    Dim ws As Worksheet, co As ChartObject, ch As Chart
    
    For Each ws In Worksheets
        If ws.Visible Then
            For Each co In ws.ChartObjects
                With co.Chart.Axes(xlValue)
                    .MajorUnitIsAuto = True
                    .MajorUnit = Round(.MajorUnit, 0)
                End With
            Next
        End If
    Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[idbr]

Much more elegant Skip, but unfortunately the same behaviour at all points.

The issue seems to be running it from the combo box change event. If I run the code from its own proc, it works as expected.

It seems like the chart properties are not available during the event? Is this possible?

Odd.

NB: Ran with minor edit:

    Dim ws As Worksheet, co As ChartObject, ch As Chart, [COLOR=blue]intMU As Integer[/color blue]
    
    For Each ws In Worksheets
        If ws.Visible Then
            For Each co In ws.ChartObjects
                Debug.Print co.Chart.Name
                With co.Chart.Axes(xlValue)
                    .MajorUnitIsAuto = True
                    [COLOR=blue]If Round(.MajorUnit, 0) = 0 Then intMU = 1 Else intMU = Round(.MajorUnit, 0)[/color blue]
                    .MajorUnit = intMU
                End With
            Next
        End If
    Next

 

[SkipVought]

I am also running from a combo_click event without issues...

Sub test()
    Dim ws As Worksheet, co As ChartObject, ch As Chart
    
    For Each ws In Worksheets
        If ws.Visible Then
            For Each co In ws.ChartObjects
                With co.Chart.Axes(xlValue)
                    .MajorUnitIsAuto = True
                    If .MajorUnit = 0 Then
                        .MajorUnit = 1
                    Else
                        .MajorUnit = Round(.MajorUnit, 0)
                    End If
                End With
            Next
        End If
    Next
End Sub

Private Sub ComboBox1_Change()
    test
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[idbr]

Interestingly, it seems to matter where the external proc is located. I had the code sitting in the workbook's module, moving it to an external module seems to help.

It now runs as expected from the exernal call within the event. I'm still throwing the automation error occasionally though:

1004: Method 'Axes' of object '_Chart' failed (Thrown by VBAProject)

Are you seeing this too?

 

[SkipVought]

No. Runs without error.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[idbr]

Hmm, odd. Confused. %-(

Must have something to do with a conflict between operations, will try and track it down and let you know how I get on...

 

[idbr]

Have run a bit of testing and the problem does seem to be caused by running the code within the combo_change event.

The complicating factor is that I have two combo boxes, cbo2 is dependent on the selection in cbo1.

As a result, cbo2's change event fires in the middle of cbo1's change event. Each event calls a proc in an independent module.

If I place the call to the reaxis proc in the change event for cbo2 only, this generates a call stack (whilst mid redo of axes) that looks something like this:


reaxis_proc
cbo2_proc
cbo2_change
<non-basic-code>What is this??? Maybe event sequence handling routines?
cbo1_proc
cbo1_change

Holding the reaxis proc with breakpoints and testing the value of MajorUnitIsAuto after setting it to True in the proc shows that the changes are not being applied to the chart.

If I add a call to reaxis at the end of cbo1_proc, the changes happen as a result of the second call.

So, it would seem like a problem with nested events. This is borne out by the 1004 error behaviour:

This error occurs every time I make a change in cbo1, after making a change in cbo2 (the cbo2 call on its own works just fine btw). On calling the cbo1 proc, the 1004 error is still thrown by cbo2_proc's call to reaxis and the changes don't get made at this point, however it isn't terminal and the cbo1 call to reaxis works and makes the changes.

It's kludged and working so I'm not going to investigate further, but I'd be interested in your thoughts?

Cheers, Iain