XL97: Run-Time Error in Macro Assigned to Control

ID: Q169103

The information in this article applies to:

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 professionals 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          : kberrmsg xlui xlvbahowto xlvbainfo 
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbprb

Last Reviewed: May 17, 1999