Results 1 to 5 of 5

Thread: macro for protect/unprotect that prompts for password!!!!
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,598
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default

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

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

    Code:
    ActiveWorksheet.Unprotect("password")
    will unprotect with no dialog (if "password" is right)

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

  3. #3
    New Member
    Join Date
    Dec 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  4. #4
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,598
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default

    Code:
    ActiveWorksheet.Unprotect
    automaticaly brings up the password request box when the sheet is protected.

  5. #5
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,598
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default

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

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •