Protect and Unprotect Specific Worksheets

CB1994

New Member
Joined
Jun 29, 2016
Messages
14
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;

Code:
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


Code:
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

tygrrboi

Well-known Member
Joined
Sep 8, 2015
Messages
1,196
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

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

'Do Something

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

CB1994

New Member
Joined
Jun 29, 2016
Messages
14
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

Code:
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.
 

tygrrboi

Well-known Member
Joined
Sep 8, 2015
Messages
1,196

ADVERTISEMENT

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

Code:
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

Code:
Call UnprotectOneSheet (Sheets("Sheet1"))

'do something

Call ProtectOneSheet (Sheets("Sheet1"))
 

CB1994

New Member
Joined
Jun 29, 2016
Messages
14
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

New Member
Joined
Jun 29, 2016
Messages
14
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
 

Forum statistics

Threads
1,141,309
Messages
5,705,672
Members
421,404
Latest member
Mikecollo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top