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

ID: Q71315

The information in this article applies to:

Microsoft Excel 97 for Windows

SYMPTOMS

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:

   Run-time error '438'

   Object doesn't support this property or method.

CAUSE

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.

RESOLUTION

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:

   ARTICLE-ID: Q151261
   TITLE     : OFF97: How to Obtain and Install MS Office 97 SR-2

STATUS

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).

MORE INFORMATION

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.

Additional query words:

Keywords          : kbdta
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbbug
Solution Type     : kbfix

Last Reviewed: November 3, 1998