[SOLVED] Excel 2007 chartobjects

ryana · #1 11-14-2008, 05:07 AM

I have a problem involving the chartobjects collection as documented in the following VBA code:

Code:

Sub ReLocateLegend()
     ' This code (and other sequences involving chartobjects)
     ' works fine under previous versions
     ' but under XL2007 halts at the first line with a run time error:
     ' Run-time error '1004' - "Application-defined or object-defined error"
     
     Worksheets("Graph").ChartObjects(1).Activate ' halts here with 1004 error
     With ActiveChart.Legend
          .Left = 15
          .Top = 259
     End With

End Sub


Sub ReLocateLegend2()
    ' This functionally identical code was recorded with the XL2007 Macro 
    ' Recorder but also halts (under XL2007) at the first line with the same 
    ' runtime error:
    ' Run-time error '1004' - "Application-defined or object-defined error"
  
    ActiveSheet.ChartObjects("Chart 70").Activate  ' halts here with 1004 err
    ActiveChart.Legend.Select
    Selection.Left = 24.75
    Selection.Top = 262.5

End Sub



Sub ReLocateLegend3()
    ' Information from the object browser suggests the insertion of 'Chart' 
    ' as below. This may be progress? but we have a new error message:
    ' Run-time error '1004' - "Activate Method of Chart Class Failed"
  
    ActiveSheet.ChartObjects("Chart 70").Chart.Activate  
         ' halts at line above with 1004 error
    ActiveChart.Legend.Select
    Selection.Left = 24.75
    Selection.Top = 262.5

End Sub

The complete program uses 'chartobjects' in several other ways - all worked as expected under earlier versions but not under XL2007

Can anyone please advise how to get this code working under XL2007

**Andy Pope** · #2 11-14-2008, 06:59 AM

Only ReLocateLegend3 failed for me, which is to be expected.

Do you have SP1 installed?

ryana · #3 11-17-2008, 07:57 AM

Thanks Andy

I have now.

Unfortunately there is no improvement.

**Andy Pope** · #4 11-17-2008, 07:58 AM

Can you post example file?

ryana · #5 11-19-2008, 06:55 AM

Hi Andy

The requested example file is attached. I have included a second example of the use of chartobjects as the original one is a bit naff.

However, while pruning 99.9% of the code, 99.99% of the data and making a few other changes the problem resolved itself.

Investigation has since disclosed that unprotecting the worksheet made the difference.

I suppose it's only reasonable that XL07 should perform differently from previous versions in this respect! And being able to perform actions manually with the sheet protected (and have the macro recorder record them) but then not have the code run without trouble is a stunning bit of deception.

As yet I havn't found any way of "Unlocking" the graph so sheet protection doesn't affect it. If all else fails I expect that I will be able to bracket the errant statement(s) with Unprotect and Protect statements.

Thanks very much for your help it has successfuly concluded more than 6 months off and on effort.

cheers

**Andy Pope** · #6 11-19-2008, 07:20 AM

Thanks for the example file.

The protection and VBA interaction are definite bugs which I will report.

Yes you will need to add code to remove/replace protection before executing code to manipulate chart object

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)