Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/169103

From BetaArchive Wiki
Knowledge Base


Article ID: 169103

Article Last Modified on 10/21/2000



APPLIES TO

  • Microsoft Excel 97 Standard Edition



This article was previously published under Q169103

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.

MORE INFORMATION


Additional query words: XL97 command button list box combo check option ocx edit

Keywords: kberrmsg kbprb KB169103