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.

 

[JasonC71]

Before the Copy/Paste thing put:

ThisWorkbook.Unprotect ("Password")

After copy/paste thing runs put:
ThisWorkbook.Protect ("Password")

replace Password with your password..but make sure to use ""

 

[VoG]

You can do that in code like this:

Sheets("Sheet1").Unprotect Password:="abc"
'
'do your stuff
'
Sheets("Sheet1").Protect Password:="abc"

 

[dfenton21]

Excellent, thank you.

 

[GTO]

Presuming that you are just getting the value, you can also do this w/o ever unprotecting the sheet, by setting the UserInterfaceOnly arg to true - like:

    With ThisWorkbook.Worksheets("MySheet")
        .Protect "MyPassword", , , , True
        .Range("A3").Value = Worksheets("OtherSheet").Range("A5").Value
    End With

Mark

 

[dh_ander]

I read the reply of Vog and this would be a base for what I need
which is 1000 Excelbooks that contain multiple protected sheets and now I need to:
a) Unprotect all sheets that are protected
b) Do my stuff
c) Return sheets that where protected before to protected state

any suggestions

Thanks in advance
David Sanders, Amsterdam

 

[Stormseed]

David,

Are you using the same password to protect all the worksheets in your workbook ?

 

[dh_ander]

Yep al sheets have same password

 

[Stormseed]

hi again,

I am providing you with the code. The macro below will help you to Protect / UnProtect all the worksheets in one go in your workbook. Please copy and paste all of the code in one standard module:

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]

This is real great and so fast!

The only "but" in this code is that it protects "all" the sheets even the sheets that where before unprotected.