Protect a whole sheet including all tabs

Geek Girl 007

Board Regular
Joined
Mar 12, 2022
Messages
74
Office Version
  1. 2021
Platform
  1. Windows
I often have to protect many cells (with formulas) on a sheet, however if I have many tabs in that workbook I have to unprotected them 1 by 1.
Can I do all of them with 1 password???
I've tried 'protect workbook' but I could still edit the 'locked' cells.
1649346961897.png
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Jamie McMillan

Board Regular
Joined
Nov 8, 2021
Messages
169
Office Version
  1. 2016
Platform
  1. Windows
Hello,

Sub UnProtect()
Dim ws As Worksheet
For Each ws In Worksheets
ws.UnProtect "Password"
Next ws
End Sub

Sub Protect()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect "Password"
Next ws
End Sub

Jamie
 

Geek Girl 007

Board Regular
Joined
Mar 12, 2022
Messages
74
Office Version
  1. 2021
Platform
  1. Windows
HI,
Thanks for this.
It's defiantly move advanced than the level I am at.
But thank you .
 

Jamie McMillan

Board Regular
Joined
Nov 8, 2021
Messages
169
Office Version
  1. 2016
Platform
  1. Windows
Hello,

Does it work???

Or do you need some more help???

Jamie
 

Geek Girl 007

Board Regular
Joined
Mar 12, 2022
Messages
74
Office Version
  1. 2021
Platform
  1. Windows
Hi Jamie,
I'm sure it does but I'm not quite at the 'macro' stage.
I had a go at adding it but I could still edit the 'protected' cells

It's basically a document that, uses 'vlookup' to get information from 2 other sheets, and when those sheets are updated it leaves blanks on this one.
Many other people use the sheet and I 'password protect' each tab so that other people won't accidentally delete the formula or mess up the sheet.

I was looking for a way of only having to add only 1 password to unlock all the sheets for editing rather than 1 per sheet.
Does that all make sense??

However, I am excel basic so think this may be too advanced for me.
Thanks
 

Jamie McMillan

Board Regular
Joined
Nov 8, 2021
Messages
169
Office Version
  1. 2016
Platform
  1. Windows
Hello, :)

First:- Are you sure that the cell you can edit is locked??? (Right click the cell, when unprotected, choose format cells, then protection: make sure there is a tick in the 'locked' box.)
Second:- Are you sure that the sheet is protected??? (You cannot edit a locked cell if the sheet is protected)

I assume that you are not hiding the sheet tabs.

So I will talk you through - what I think will be best for you. And Maybe fix "when those sheets are updated it leaves blanks on this one". (I think the sheet may be on manual calculate.)

1st Insert a new sheet (We will hide that later; so nobody can see it.)
2nd Right click on the new sheet tab and choose rename - call it "Mine"
3rd Insert a shape, square, circle, anything.
4th select and copy the code below.


VBA Code:
Dim ws As Worksheet
    Application.Calculation = xlAutomatic
    If Range("A1").Value = 1 Then
    Range("A1").Value = 0
    For Each ws In Worksheets
    ws.Protect "Password"
    Next ws
    Else
    For Each ws In Worksheets
    ws.Unprotect "Password"
    Next ws
    Range("A1").Value = 1
    End If
    Sheets("Mine").Select


5th Right click on the shape you inserted, select 'assign macro' and select 'New': And paste the copied code, when it opens.

Now when you click on the shape, if the sheet is protected it will unprotect it and vice versa.

6th right click on the sheet named 'Mine' and select "Hide"

Now nobody can see it.

To unhide it - right click on any sheet tab and select 'Unhide'

So now if you want to unprotect it; unhide the sheet "Mine" click on the shape - to protect it click on the shape and hide the sheet "Mine" (The password is Password - you can change that in the macro above.)

Remember to check that the cells are actually 'locked'.

Now you are using macros. :)

Jamie
 
Solution

Geek Girl 007

Board Regular
Joined
Mar 12, 2022
Messages
74
Office Version
  1. 2021
Platform
  1. Windows
Oh wow,
I had a play with it at home and i think it's worked.
I will have a go at assigning it to the sheet I need at work tomorrow and let you know, but so far this is looking SUPER.
Thank you
 

Geek Girl 007

Board Regular
Joined
Mar 12, 2022
Messages
74
Office Version
  1. 2021
Platform
  1. Windows
Hello, :)

First:- Are you sure that the cell you can edit is locked??? (Right click the cell, when unprotected, choose format cells, then protection: make sure there is a tick in the 'locked' box.)
Second:- Are you sure that the sheet is protected??? (You cannot edit a locked cell if the sheet is protected)

I assume that you are not hiding the sheet tabs.

So I will talk you through - what I think will be best for you. And Maybe fix "when those sheets are updated it leaves blanks on this one". (I think the sheet may be on manual calculate.)

1st Insert a new sheet (We will hide that later; so nobody can see it.)
2nd Right click on the new sheet tab and choose rename - call it "Mine"
3rd Insert a shape, square, circle, anything.
4th select and copy the code below.


VBA Code:
Dim ws As Worksheet
    Application.Calculation = xlAutomatic
    If Range("A1").Value = 1 Then
    Range("A1").Value = 0
    For Each ws In Worksheets
    ws.Protect "Password"
    Next ws
    Else
    For Each ws In Worksheets
    ws.Unprotect "Password"
    Next ws
    Range("A1").Value = 1
    End If
    Sheets("Mine").Select


5th Right click on the shape you inserted, select 'assign macro' and select 'New': And paste the copied code, when it opens.

Now when you click on the shape, if the sheet is protected it will unprotect it and vice versa.

6th right click on the sheet named 'Mine' and select "Hide"

Now nobody can see it.

To unhide it - right click on any sheet tab and select 'Unhide'

So now if you want to unprotect it; unhide the sheet "Mine" click on the shape - to protect it click on the shape and hide the sheet "Mine" (The password is Password - you can change that in the macro above.)

Remember to check that the cells are actually 'locked'.

Now you are using macros. :)

Jamie
This is BRILL, Thank you so much :)
 

Forum statistics

Threads
1,175,792
Messages
5,899,517
Members
434,778
Latest member
Alina N

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