VBA Protection

[dfenton21]

I have a macro that copies the contents of a cell, and pastes it into the the first blank cell of a range. Its important that the entire sheet is protected, but the macro won't allow the paste function because of the protection.

Is there a VBA code to unprotect the sheet, run the copy/paste macro, then protect the sheet again. THe problem is I would prefer the protection to use a password, as I don't want the user to simply unprotect the sheet from the menu bar.

Any assistence would be greatly appreciated.

Thanks.

 

[Stormseed]

For the sheets that you do not want protection, Go to the sheet, Press Ctrl + A, Press Ctrl + 1, Protection tab and uncheck the "Locked" option and click on OK.

You are done

Edit: The other way would be to change the code in order for the macro to run only on specific sheets but that would take some extra time and coding in the present code.

I would go for the uncheck "Locked" option.

 

[dh_ander]

That should should do it

Many thanks!

 

[dh_ander]

While using the code I noticed that some sheets have a different passwords, for me these errors are not interested.
How do I ignore this error and continue unprotecting the next sheet?

 

[Stormseed]

Replacing the exsiting code with this code will stop displaying the "Incorrect Password" error message:

Option Explicit
 
Sub Protect_all()
     
    Dim wSheet          As Worksheet
    Dim Pwd             As String
     
    Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
    For Each wSheet In Worksheets
        wSheet.Protect Password:=Pwd
    Next wSheet
     
End Sub
 
Sub UnProtect_all()
     
    Dim wSheet          As Worksheet
    Dim Pwd             As String
     
    Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")
    On Error Resume Next
    For Each wSheet In Worksheets
        wSheet.Unprotect Password:=Pwd
    Next wSheet
   'If Err <> 0 Then
   '   MsgBox "You have entered an incorect password. All worksheets could not " & _
   '   "be unprotected.", vbCritical, "Incorect Password"
   ' End If
    On Error GoTo 0
     
End Sub

 

[dh_ander]

It certainly does the Job, but now I noticed that when setting the password again it crashes on, I guess password protected sheet.
So how do I check a sheet is already protected and then skip this sheet.

Sorry for the bother but I really do appreciate your help.