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