Protect a whole sheet including all tabs

[Geek Girl 007]

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.

 

[Jamie McMillan]

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]

HI,
Thanks for this.
It's defiantly move advanced than the level I am at.
But thank you .

 

[Jamie McMillan]

Hello,

Does it work???

Or do you need some more help???

Jamie

 

[Geek Girl 007]

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]

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.

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

 

[Geek Girl 007]

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]

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.

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

 

[Geek Girl 007]

Hi all,
I need help with this MACRO....
Yes it worked perfectly, how do I use the same MACRO to work and my staff still be able to FILTER???
Can this be done?