You are prompted for worksheet password when you use the UserInterfaceOnly=True Protect method

In Microsoft Excel 2002 and in later versions of Excel, 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 earlier versions of Excel, you are not prompted for a password when you use this method.

CAUSE

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. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. Use the following code to avoid being prompted for a password. where MyPassword is the password string used to protect the worksheet.

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.