Password Protected Command Button to delete multiple selections..still clears cells when no pswd is entered and cancel is selected

[djrobr199]

I have a command button that will delete multiple ranges and I have it password protected. All of my sheets are password protected so I have it automatically unprotecting the sheet and supplying a new password so that it would prompt the user to enter the new password (which they will know) in order for them to clear the ranges (to prevent accidental clearing of data), then it would protect the sheet with the original password when all the ranges have been cleared. The problem i'm running into is that if the user selects the command button and then selects the cancel button or red X to close out (if they selected it by mistake) it still clears out the ranges. If they select OK without entering a password then all the data remains as it should (via the error handling). Is there a way to fix this problem?

(# represents my password that the user does not have access to)
(@ represents the password that the user will know)

Private Sub CommandButton5_Click()
' clear out all unit counts and yard count clear time
On Error GoTo errorfound
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:="######"
ActiveSheet.Protect Password:="@@@@@@@"
ActiveSheet.Unprotect
Range("E6:E9").Select
Selection.ClearContents
' There are many more ranges but removed them to make this post smaller
Range("A5").Select
Selection.ClearContents
ActiveSheet.Protect Password:="######"
Application.ScreenUpdating = True
Range("E6").Select
errorfound:
Sheets("master count input").Select
ActiveSheet.Unprotect Password:="@@@@@@@"
ActiveSheet.Protect Password:="######"

End Sub

 

[mikerickson]

Rather than un-protecting and re-protecting sheets with various passwords, why not use your own password checking routine as a gate-keeper.
Something like

Do
    uiPW = InputBox("Enter your Password")
    If uiPW = "" Then Exit Sub
Loop Until uiPW = "@@@@@"

With ActiveSheet
    .Unprotect "####"
    .Range("A1:A10").ClearContents
    Rem more clearing

    .Protect "####"
End With

The problem with this approach is that InputBox won't run when a userform is running. (What is posted above won't run.)

You'ld have to either make a userform that emulates an input box.
Or make the password entry controls one page of a multi-page.

 

[djrobr199]

Thank you so much for your help!! Everything worked like a charm. Also an unintended benefit is that you showed me how to clean up the code a lot. For the past 6 months or so I have been learning VBA on my own. While I have learned a lot from books and online there is still a lot I have to learn. I am creating a program for my work (on my own) basically because I love doing this stuff. (I am in Law Enforcement, but I do have a passion for computers) I have always loved Excel and what it can do, but when I discovered the world of VBA programming it has opened my eyes to an entirely different world! I am eternally grateful for forums like this, keep up the good work.