Activate method of ChartObject class failed

[sdahl]

Excel 2000
Windows XP

I have a workbook that opens another workbook, copies a worksheet into that workbook and runs functions from the copied worksheet. All of this works as expected until I try to activate a chart object within one of the functions.

To try and be more clear as to what the process is:

• Workbook A contains Worksheet B that contains code.
• Workbook A contains code that opens Workbook X and copies Worksheet B into Workbook X.
• Workbook A then calls a function from the copied version of Worksheet B in Workbook X. This is where the code fails.

The workbook and worksheets are not protected. There are no other users accessing the worksheet as it's presently on a local drive. I tried the Application.Interactive = false, but that did not help. I also tried "unprotecting" the workbook/sheet even though they aren't protected to begin with.

I've tried different sytax to activate the chart, all of which yeild the same stinking error "Activate method of ChartObject class failed".

Here are a few different ways I've tried to activate the sheet:

with ActiveSheet
   ...
   .ChartObjects("Chart 3").Activate
   ...
end with

   ThisWorkbook.Sheets(SHEET_NAME).ChartObjects(3).Activate

   ThisWorkbook.Sheets(SHEET_NAME).ChartObjects("Chart 3").Activate

I've also done this:

for each co in activeSheet.ChartObjects
   msgbox co.name
next

I get a message box for each chart including "Chart 3".



And yes, it is absolutely necessary that one workbook opens another the way I described in the first paragraph.

 

[Andrew Poulsom]

You shouldn't have a problem activating a chartobject on the active sheet (provided it exists). Please post the code that's failing.

 

[sdahl]

You shouldn't have a problem activating a chartobject on the active sheet (provided it exists). Please post the code that's failing.

Private Sub UpdateLines(strABC As String)
    Dim lCol As Long, i As Integer, ABC As String, seriesNumber As Integer, colorCode As Byte
 
    ' locations in the Plots sheet
    Const ROW_TOP_SPEED = 3
    Const COL_X = 9
    Const COL_BASE = 10
 
    With ActiveSheet
 
        msgbox(.ChartObjects("Chart 3").name)
        .ChartObjects("Chart 3").Activate
 
        For i = 1 To 4
 
            If i = 1 Then
                ABC = "1"
                seriesNumber = 1
            ElseIf i = 2 Then
                ABC = "2"
                seriesNumber = 2
            ElseIf i = 3 Then
                ABC = "3"
                seriesNumber = 4
            ElseIf i = 4 Then
                ABC = "s"
                seriesNumber = 5
            End If
 
            If InStr(strABC, ABC) = False Then
 
                ' make the line invisible
                ActiveChart.SeriesCollection(seriesNumber).Select
                Selection.Border.LineStyle = xlNone
 
                ' move line's data to column +5, which should be SPACES
                lCol = COL_BASE + 5
                updateChartSourceData .name(), "Chart 3", ROW_TOP_SPEED, COL_X, ROW_TOP_SPEED, lCol, CLng(seriesNumber), True
 
            End If
        Next i
 
    End With
End Sub

I bolded the line that errors. Please note that the message box works and properly displays the message "Chart 3".

 

[Andrew Poulsom]

I can reproduce your error if the worksheet is protected.

 

[sdahl]

I can reproduce your error if the worksheet is protected.

It's definitely not protected. Just to be sure I did a .unprotect just prior to activating the chart and got the same error. Then I tried to .protect "FOOBAR" followed by .unprotect "FOOBAR" and still received the same error.

The strangest thing about this is that in the updateChartSourceData function call later in that code, the same chart is activated using the same code I am and it does not error. I commented out all my code in that function that referenced the chart and stepped into the updateChartSourceData code to verify that it does in fact activate the chart without issue.

 

[sdahl]

So because there was a function already correctly activating the chart I just moved what I needed to do into that function by passing an additional value (optional parameter). It would be interesting to know why I was unable to activate the chart in the original context but sometimes it's best to just let something go.

 

[TheEnergyMan]

As Ive just spent the better part of an hour looking for my solution to this error i thought id put it here,

my case was that i was activating the same chart twice when it wasnt unselected.

wshome.ChartObjects("Chart 4").Activate
'other code
'other code
'other code
'other code
'other code
'other code
wshome.ChartObjects("Chart 4").Activate

I was running Excel 2010 and never got this issue but not run excel 2016 and it came about.

Hope this helps anyone else