You Are Prompted for Worksheet Password When You Use the UserInterfaceOnly=True Protect Method (810788)

SYMPTOMS

In Excel 2002, when you use the Protect method with the UserInterfaceOnly argument set to True (UserInterfaceOnly=True) to access a password-protected worksheet, you are prompted for a password.

In previous versions of Excel, you are not prompted for a password when you use this method.

CAUSE

This behavior is by design. The password-protection functionality does not work in previous versions of Excel when you use this method. This issue has been corrected in Excel 2002. This prevents a user from accessing a password-protected worksheet.

RESOLUTION

To avoid being prompted for a password when you use this method, you can use the Password argument. The following method works in all versions of Excel.

Microsoft provides programming examples 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. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, see the following Microsoft Web site:

http://www.microsoft.com/partner/referral/

For additional information about the support options available from Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

Use the following code to avoid being prompted for a password

ActiveSheet.Protect Password:="MyPassword", UserInterfaceOnly:=True

where MyPassword is the password string used to protect the worksheet.

MORE INFORMATION

The Protect Method

The Protect method protects a worksheet or workbook so that it cannot be modified.

Use the Protect method with the Password argument to specify a case-sensitive password for the worksheet or workbook. If this argument is omitted, you can unprotect the worksheet or workbook without using a password. Otherwise, you must specify the password to unprotect the worksheet or workbook. If you forget the password, you cannot unprotect the worksheet or workbook. Microsoft recommends that you keep a list of your passwords and their corresponding document names in a safe location.

When you use the Protect method with UserInterfaceOnly argument set to True (UserInterfaceOnly=True), you protect the user interface but not macros. If this argument is omitted, protection applies both to macros and to the user interface.

If you apply the Protect method with the UserInterfaceOnly argument set to True to a worksheet, and then you save the workbook, the whole worksheet (not just the interface) will be fully protected when you reopen the workbook. To re-enable the user interface protection after the workbook is opened, you must again apply the Protect method with UserInterfaceOnly set to True.

If you want to make changes to a protected worksheet, you can use the Protect method on a protected worksheet if the password is supplied. You can also unprotect the worksheet, make the necessary changes, and then protect the worksheet again.

Note Unprotected" means that although a cell may be locked, the cell can be accessed because the cell is included in a range defined in the Allow Users to Edit Ranges dialog box, and the user has either unprotected the range with a password or the range has been validated through Windows NT permissions.

For more information about the Protect method, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type protect method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.