MXL5: Range Method May Fail When Used in Auto_Open Macro

ID: Q126045

The information in this article applies to:

Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SYMPTOMS

When you open a Microsoft Excel workbook by double-clicking the workbook icon or alias in the Finder, you may receive the following error message:

Run-time error '1004':

Range Method of <object> Class Failed

CAUSE

This error message occurs when you open a workbook by double-clicking the file if the file contains an Auto_Open macro that uses the Range method to select a specific cell or range of cells. For example, this error message appears when you open a workbook (by double-clicking the workbook icon in the Finder) that contains the following Visual Basic macro:


   Sub Auto_Open
      Sheets("Sheet2").Select
      Range("A1").Select
   End Sub

The Range method fails when it is used with the Select method in any of the following situations:

An Auto_Open macro

A macro that is called by an Auto_Open macro

Any macro assigned to a dialog control whose dialog box is displayed by an Auto_Open macro

Note that the Range method does not fail in these situations if the workbook is opened from within Microsoft Excel.

WORKAROUNDS

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

To avoid receiving this error message when you use the Range method in a Visual Basic macro, use any of the following methods:

Method 1

Open the workbook that contains the Auto_Open macro within Microsoft Excel by clicking Open on the File menu instead of opening the file in the Finder.

Method 2

Use the Cells method instead of the Range method in the situations described above. For example, the following statement in a Visual Basic macro


   Range("A1").Select

could be changed to the following:


   Cells(1,1).Select

Method 3

Use the Offset method to refer to the cell in terms of its relationship to the active cell instead of using the Range method.

For example, to select cell A1 using the Offset method, you can use the following statement:


   ActiveCell.Offset(-1*Activecell.Row+1, _
      -1*Activecell.Column+1).Select

To select cell B5, you can use the following statement:


   ActiveCell.Offset(-1*Activecell.Row+5, _
      -1*Activecell.Column+5).Select

Note that you must also use the Cells method or the Offset method instead of the Range method in all subsequent lines in the Auto_Open macro, or in any macro called by the Auto_Open macro.

Method 4

Use the Resize method to refer to a contiguous range of cells.

For example, to select cells A1 through C5, you can use the following statement:


   Cells(1,1).Resize(5,3).Select

Method 5

Use the Union and Cells methods to refer to discontiguous ranges of cells.

For example, to select cells A1 through C5 and cells E11 through F15, use the following statement:


   Union(Cells(1,1).Resize(5,3), _
      Cells(11,5).Resize(5,3)).Select

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The following are different scenarios in which the Range method returns the run-time error described above.

Scenario 1

If you use the Range method in an Auto_Open macro for a workbook, you may receive this error. For example, you receive the run-time error when you double-click a workbook in the Finder that contains the following macro:


   Sub Auto_Open()
      Worksheets(1).Select
      Range("A1").Select
   End Sub

Scenario 2

If you use the Range method in a macro that is called from an Auto_Open macro in a workbook, you may receive this error. For example, you receive the run-time error when you double-click a workbook in the Finder that contains the following macros:


   Sub Auto_Open()
      Macro1
   End Sub

   Sub Macro1()
      Worksheets(1).Select
      Range("A1").Select
   End Sub

Scenario 3

If the Range method is used in any macro that is assigned to a dialog box control and the dialog box is displayed in an Auto_Open macro, this error may also occur. For example, if BOOK1 contains a dialog sheet "Dialog1" that contains a button "Button 1," and "Button 1" is assigned to the macro "Button1_Click," you receive the run-time error when you double-click BOOK1 in the Finder, and then click "Button 1":


   Sub Auto_Open()
      Dialogsheets("Dialog1").Show
   End Sub

   Sub Button1_Click()
      Worksheets(1).Select
      Range("A1").Select
   End Sub

Additional query words: 5.00a

Keywords : kbcode kbprg xlmac
Version : MACINTOSH: 5.0,5.0a
Platform : MACINTOSH
Issue type : kbbug