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

• 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:

Q151261 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

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