macro for protect/unprotect that prompts for password!!!!

[whippler]

i have a workbook that admin users will be using...i have the book locked for regular users, but i have some buttons that the admins will click to unhide certain information...how can i set the button that unprotects, then unhides some data so that a prompt comes up to enter the password?....this way i can restrict to regular users as they won't have the password

thx

 

[mikerickson]

Dim xVal As Boolean
xVal = Application.Dialogs(xlDialogProtectDocument).Show _
    (True, False, "default", True, True)
' (checkContentsBox,??,defautPassword,checkObjectsBox,checkSecnariosBox) optional arguments

will show the standard PROTECT WORKSHEET dialog box, with "default" ready to go as the password.
It will return xVal = TRUE if the sheet is succesfuly protected. FALSE if the user cancels

ActiveWorksheet.Unprotect

will invoke the "enter password to unprotet" dialog box, if it is needed.

ActiveWorksheet.Unprotect("password")

will unprotect with no dialog (if "password" is right)

(Does anyone know what the second term of the Show. sub does?)

 

[whippler]

prompt

i need to know how to bring up the password prompt for user entry when executing a macro that needs to unlock the sheet (it unhides columns)

how do i do that....protecting it is easy

 

[mikerickson]

ActiveWorksheet.Unprotect

automaticaly brings up the password request box when the sheet is protected.

 

[mikerickson]

I've done some checking and have found that this instruction is context sensitive.

Dim xVal As Boolean
xVal = Application.Dialogs(xlDialogProtectDocument).Show
'  [opt.arguments](contents,windows,passwordString,drawingObjects,scenarios)

If the ActiveSheet is unprotected, the standard Protect Sheet box will appear as will the Confirm Password if nessessary.
If the user Cancels, the box goes away, xVal=FALSE and the macro continues.
Otherwise, the sheet is protected (perhaps with the password entered by the user), xVal=TRUE and the macro continues.

If the ActiveSheet is protected without a password, no box appears, the sheet is unprotected and the macro continues.

If the ActiveSheet is protected with a password,the Unprotect Worksheet box appears, and

If the User Cancels, the sheet remains protected, xVal=FALSE and exicution continues
If the user enters the correct password, the sheet is unprotected, xVal=TRUE and ...
If the user enters the wrong password, a VB error occurs, but if this is ignored, xVal=FALSE.

When protecting a sheet, the optional arguments are the default settings of the box as it appears. (Microsoft calls the second argument "windows" which suggests that there is a context in which this command will display the Protect Workbook box.) If there is a default password, the user must manualy enter it into the Confirm Password box.

When unprotecting a (passworded) sheet, it seems that if any of the boolean arguments are TRUE, the Unprotect box does Not appear, the sheet is left protected and xVal=TRUE.

If all arguments are missing, the Unprotect Sheet box appears and behaves as above.

If the correct password is entered as an argument, the sheet is unprotected and xVal=TRUE.
If an incorrect password is entered as the argument, the sheet remains protected and xVal=FALSE.
In neither of these two cases does any box appear, not even a "Check your CAPS LOCK" box. Most significantly, an incorrect password does not generate a VB error.

In general, the returned xVal (and the error generated by a user attempting a wrong password) return more information to your macro than the sheet.Unprotect method of invoking the Unprotect Sheet box.

One reason that you might find this useful, is that when an administrator protects the worksheet, setting a default value for the password will help prevent an administrator from mis-entering the community password (big oops) and the Confirm screen will confirm that it is an administator who is protecting the sheet.