Protect and Unprotect Specific Worksheets

[CB1994]

Hello All,

I would like a Macro/s that is/are able to protect and unprotect specific worksheets. This is so that other Macros can then access the data on these sheets.

Currently I have a Macro, taken from another thread, that Protects and Unprotects all of the worksheets. This works fine, however it takes quite a while and I had hoped having a Macro to Protect/Unprotect only the worksheets that are needed would be beneficial. Below is the code I have to Protect/Unprotect all worksheets;

Sub UnprotectAll()


Application.ScreenUpdating = False


    Dim sh As Worksheet
    Dim yourPassword As String
    yourPassword = "111"


    For Each sh In ActiveWorkbook.Worksheets
        sh.Unprotect Password:=yourPassword
    Next sh


Application.ScreenUpdating = True


End Sub

Sub ProtectAll()


Application.ScreenUpdating = False


Dim sh As Worksheet
Dim yourPassword As String
    yourPassword = "111"


    For Each sh In ActiveWorkbook.Worksheets
        sh.Protect Password:=yourPassword
    Next sh


Application.ScreenUpdating = True


End Sub

Any help you can give me would be greatly appreciated.

Kind regards.

Connor

 

[tygrrboi]

If you know the name of the worksheets you could just do the code for the ones you need without looping through them all.. For example

Sheets("Sheet1").Unprotect Password:=yourPassword
Sheets("Sheet3").Unprotect Password:=yourPassword

'Do Something

Sheets("Sheet1").Protect Password:=yourPassword
Sheets("Sheet3").Protect Password:=yourPassword

 

[CB1994]

If you know the name of the worksheets you could just do the code for the ones you need without looping through them all.. For example

Sheets("Sheet1").Unprotect Password:=yourPassword
Sheets("Sheet3").Unprotect Password:=yourPassword

'Do Something

Sheets("Sheet1").Protect Password:=yourPassword
Sheets("Sheet3").Protect Password:=yourPassword

Thats great, I'll give it a go now.

So doing this means I wouldn't need a Macro to Protect/Unprotect. I would just need to put those lines of code into the existing Macros?

Thank you.

 

[CB1994]

Thank you tyggrboi, it works perfectly, and is noticeably faster.

 

[tygrrboi]

Put it into the existing macros.

If your password is always going to be the same you do not need to go through the trouble of declaring a password string for each macro that its in...

Just input it in quotes.

Sheets("Sheet1").Unprotect Password:="applesandbananas"

for example.



Or Create macro that protects or unprotects just one sheet and you pass it the information

Private Sub UnprotectOneSheet(ws as worksheet)
  ws.Unprotect Password:="applesandbananas"
End Sub

Then in your existing macros... just call them at the start and at the end

Call UnprotectOneSheet (Sheets("Sheet1"))

'do something

Call ProtectOneSheet (Sheets("Sheet1"))

 

[CB1994]

Putting it in the existing Macro works exactly how I wanted it too, so I'll save myself the trouble and stick to doing it that way.

Thank you very much.

 

[CB1994]

I have another related problem. The code works in that it successfully unprotects/protects the worksheet, however the cells that I had previously defined using 'Allow Users to Edit Ranges' can no longer be edited. Is there a way that I can specify in the code which cells should effectively not be protected, or should be unprotected again after the sheet has been protected?

Regards.

Connor