CODEWITHSUNDEEP
vbaexcel
Dan
Dan

Reputation: 78

PivotChart: Can't move title without activating the chart

I am writing a little macro in Excel 2010 to move the chart title of a pivot chart, and I can get it to work using a method that activates the chart and selects the chart's title. Here is that code

Private Sub Worksheet_Calculate()

    'On Error GoTo GetOut
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    ActiveSheet.ChartObjects("Chart 1").Activate
    If ActiveChart.HasTitle = True Then
    ActiveChart.ChartTitle.Select
            With Selection
                .Left = 311.982
                .Top = 9.559
            End With
    End If

GetOut:
    ActiveSheet.Range("M21").Select
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

But I would like to avoid activating the object, so I wrote this

Private Sub Worksheet_Calculate()

Dim empChart As ChartObject

'On Error GoTo GetOut
Application.ScreenUpdating = False
Application.EnableEvents = False

Set empChart = ActiveSheet.ChartObjects("Chart 1")
If empChart.HasTitle = True Then
    With empChart.ChartTitle
        .Left = 311.982
        .Top = 9.559
    End With
End If

GetOut:
ActiveSheet.Range("M21").Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

But this gives me runtime error 438: Object doesn't support this property or method. I can't figure out why for the life of me. I'm not very good so it is probably something silly, but any help would be much appreciated

Upvotes: 0

Views: 317

Answers (1)

Tim Williams
Tim Williams

Reputation: 166316

A worksheet ChartObject has a child Chart object: you can access this directly using (eg) 

Dim cht As Chart
Set cht = ActiveSheet.ChartObjects("Chart 1").Chart

Then you can operate on cht directly instead of using ActiveChart.

Upvotes: 3

Related Questions