1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2003 Unprotect Worksheet Command Button

Discussion in 'Business Applications' started by computerman29642, Apr 14, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I have placed a command button within my excel worksheet. I would like to have a inputbox popup when the button is clicked, the user enters the worksheet password, if the password is correct then unprotect, and if the password is incorrect give a messagebox telling the user the password is incorrect.

    Any help with this would be appreciated.
     
  2. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    The Protect/Unprotect sheet button which you can add to your toolbar already has an inherent "whoops" warning when you input the wrong password. Is there a reason why you are you using a command button instead of a tool on your toolbar?
     
  3. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I believe it is quicker just to click a button on the spreadsheet while working in the spreadsheet, then click through the toolbar.
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    The macro code below should work. Just copy the code and paste behind your button click event instead of the stand alone sub procedure. This code uses an inputbox which is probably not a good idea since you can't mask the typed password. This means anyone standing over your shoulder will be able to see exactly what is typed. If you want to make it more secure you would need to create a simple password entry form and mask the text field instead of using a standard inputbox.

    Code:
    Sub UnlockWB()
    
    On Error GoTo ErrorHandle
    
    ActiveSheet.Unprotect Password:=InputBox("ENTER PASSWORD")
    MsgBox ("Worksheet Unlocked")
    Exit Sub
    
    ErrorHandle:
    
    Debug.Print Err.Description
    If InStr(1, Err.Description, "The password you supplied is not correct.") > 0 Then
    MsgBox ("Incorrect Password Entered")
    End If
    
    End Sub
    Regards,
    Rollin
     
  5. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Rollin,

    I really appreciate the code you provide, but I went with your suggestion and created a userfrom that will display when the command button is clicked. However, I cannot figure out how to mask the password.
     
  6. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Okay....I figured out how to get the password masked.
     
  7. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Click on the textbox located on your form and then look in the properties window for the property called "PasswordChar"

    This is where you enter the character to mask with (e.g. * , # , etc.)

    So basically you will have a button on the workbook to call your password form and then your code to unprotect the workbook will come from the button click event of your form.

    Regards,
    Rollin
     
  8. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Rollin,

    Is there a way to code the worksheet that if the password protection is turned off then show a command button "Protect", and if the password protection is turned on then show command button "Unprotect"?

    Could the Change_Event of the worksheet be used for this?
     
  9. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Here is the code I have not behind the OK button on the userform...

    Code:
    Private Sub cmdOk_Click()
    
        With ThisWorkbook.Sheets("Spike")
            If .ProtectContents = True Then
                .Unprotect Password:=txtPassword.Text
            Else
                .Protect Password:=txtPassword.Text
            End If
        End With
        
    Unload Me
    
    End Sub
    
    
    If the password is entered incorrectly, or left blank then a debug error message pops up. I do not want the user to be able to click the debug button. Is there a way to have a message box popup with an 'OK' and 'Cancel' buttons?
     
  10. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    You'll need to add ErrorHandling to your routine to prevent the debug diologue from appearing. See my previous code example on how to add errorhandling (post 4 of this thread)

    As far a conditional statement to determine if the sheet is protected you can use something like:

    Code:
    If ActiveSheet.ProtectContents = True Then
    ' Your code to display the unprotect button
    else
    ' Your code to display the protect button
    End If
    Regards,
    Rollin
     
  11. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Where would the conditional statement need to go?
     
  12. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Rollin,

    Here is how I changed the code under the Ok button on the userform to get rid of the error messages....

    Code:
     With ThisWorkbook.Sheets("Spike")
            
            If sPassword = txtPassword.Text Then
                .Unprotect sPassword
            Else
                MsgBox "Incorrect Password Entered", vbOKOnly
            End If
        End With
        
    Unload Me
    
    
    Do you see any problems with this code?
     
  13. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Where is sPassword being set?

    Regards,
    Rollin
     
  14. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Within the modReport module. Here is the code...

    Code:
    Public Const sPassword As String = "password"
    
     
  15. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Why are you hard coding the protect password in your code? The password is stored in the background by the application itself.

    Regards,
    Rollin
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/703711

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice