XL97: Run-time Error Using Location Method to Change Chart

When you use the Location method in a Visual Basic for Applications macro to change the location of a chart, you may receive the following error message:

This problem occurs if the following conditions are true:

• You have attached Visual Basic code to the SheetActivate event of the ThisWorkbook object. -and-
  
  
• You use the FileFormat property in the SheetActivate macro.

To correct this problem, obtain Microsoft Excel 97 Service Release 2 (SR-2).

For additional information about SR-2, please see the following article in the Microsoft Knowledge Base:

Microsoft has confirmed this to be a problem in Microsoft Excel 97.

This problem was corrected in Microsoft Office 97 Service Release 2 (SR-2).

Example of the Problem

The following steps illustrate how to reproduce the problem:

1. Type the following values into a new workbook:
   A1: 1
   A2: 2
   
2. Select cells A1:A2, and then click Chart on the Insert menu. Click Finish in the Chart Wizard dialog box.
   
   A column chart is created on Sheet1.
3. Start the Visual Basic Editor (press ALT+F11).
4. On the View menu, click Project Explorer.
5. Double-click the ThisWorkbook object.
6. Type the following code for the SheetActivate event into the module sheet:

         Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   
            y = ThisWorkbook.FileFormat
   
         End Sub
   						

7. Click Module on the Insert menu.
8. Type the following code into the module sheet:

         Sub Relocate_Chart()
   
            Dim sName As String
            Dim x As Integer
   
            Set ch = Sheet1.ChartObjects(1).Chart
            sName = Sheet1.Name
   
            For x = 1 To 2
               Set ch = ch.Location(xlLocationAsNewSheet)
               Set ch = ch.Location(xlLocationAsObject, sName)
            Next
   
         End Sub
   						

9. Run the Relocate_Chart procedure.
   
   You receive the run-time error described in the "Symptoms" section of this article.