XL97: Run-Time Error in Macro Assigned to Control

 Last reviewed: January 9, 1998
Article ID: Q169103
The information in this article applies to:
  • Microsoft Excel 97 for Windows

SYMPTOMS

When you click a control in a worksheet in Microsoft Excel 97, the Visual Basic for Applications macro assigned to the control may halt and display a run-time error message, for example: 

   Run-time error '1004':
   Copy method of Worksheet class failed
The error message varies, depending on the action the macro is attempting to perform.

CAUSE

This problem may occur if the control is still active when the macro performs certain actions, for example copying a worksheet.

WORKAROUND

Microsoft provides examples of Visual Basic for Applications procedures 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. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

To prevent this problem from occurring, modify the macro so that it deactivates the control before performing any other actions, for example: 

   Private Sub CommandButton1_Click()

       ActiveSheet.Cells(1, 1).Select   ' Deactivates the control.
       ActiveSheet.Copy                 ' Copies the worksheet.

   End Sub
If you like, you can add another line of code, just before the end of the macro that reactivates the control, for example: 

   Private Sub CommandButton1_Click()

       ActiveSheet.Cells(1, 1).Select   ' Deactivates the control.
       ActiveSheet.Copy                 ' Copies the worksheet.
       CommandButton1.Activate          ' Reactivates the control.

   End Sub

STATUS

This behavior is by design of Microsoft Excel 97.

Additional query words: XL97 command button list box combo box check box
option button ocx edit box
Keywords : xlui xlvbahowto xlvbainfo kberrmsg
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: January 9, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.