Microsoft Knowledge Base

XL: Worksheets Activated When Protection Is Applied
Last reviewed: December 17, 1996
Article ID: Q118497
The information in this article applies to:
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for the Macintosh versions 5.0, 5.0a

SYMPTOMS

In Microsoft Excel, you can use a Visual Basic for Applications macro to protect a worksheet. However, when you do this, the worksheet will be activated briefly when the protection is applied.

CAUSE

When you use a Visual Basic macro to protect a sheet, the worksheet will be activated briefly while the sheet is being protected. The focus returns to the sheet that was active at the time the routine was called. This behavior is by design of Microsoft Excel.

WORKAROUND

To avoid the screen redraw and updating while protecting a sheet in a Visual Basic macro, add the following line to your macro to set the ScreenUpdating property of the application to False: 

   Application.ScreenUpdating = False
Screen updating is set back to True when the macro ends or if you use the statement: 

   Application.ScreenUpdating = True
The following macro example protects Sheet2 without updating the screen: 

   Sub ProtectSheet2()
      Application.ScreenUpdating = False
      Worksheets(2).Protect
   End Sub

REFERENCES

For more information about the ScreenUpdating property, click the Search button in Visual Basic Help and type: 

   ScreenUpdating

KBCategory: kbprg kbcode
KBSubcategory: xlvbahowto
Additional reference words: 5.00 7.00 8.00 97 flash update refresh

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: December 17, 1996
©1997 Microsoft Corporation. All rights reserved. Legal Notices.