Password Protection

[ladytiger7481]

I have spreadsheets with multiple tabs - I presently have to password protect
each tab separately. I also have ranges within these spreadsheets that need
to be able to be unlocked with a different password. I'm looking for a way to
write a macro to be able to do this to all the spreadsheets with one click. I
have already found how to just password protect with a macro, but can someone
please show me how to add the ablity to allow user to edit ranges in the
macro. This is the macro I am presently using....

Sub Protect_All_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="123"
Next ws
End Sub

Thanks so much for any help!

 

[Jacob Skaria]

You need to unlock those cells. Suppose you want to enable the user to edit
A1:B10 range for all worksheets; try the below. Please note that the below
macro will work on the workbook where the macro is copied

Sub Protect_All_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Range("A1:B10").Locked = False
ws.Protect Password:="123"
Next ws
End Sub

If this post helps click Yes

 

[ladytiger7481]

I think I am going about this all wrong. What I need my macro to do is
protect two seperate ranges of cells say D843 and F8:F43 which have
formulas, all the other areas can be edited. However I have 31 spreadsheets
in this workbook. How should this macro be set up so that I can protect only
thoses ranges on all sheets without having to go in to all the sheets.
(Trying what you told me earlier worked but not in the way that I was looking
for it too)
Thanks!!

 

[Gord Dibben]

Sub Protect_All_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Cells.Locked = False
ws.Range("D843,F8:F43").Locked = True
ws.Protect Password:="123"
Next ws
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

You may want to unprotect all sheets at some point.

Sub UN_Protect_All_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="123"
Next ws
End Sub

I would also suggest you lock your project from viewing.

In VBE select your workbook/project and Tools>VBAProject
Properties>Protection

Viewing Protection won't take effect until you save and close/reopen the
workbook.


Gord

 

[ladytiger7481]

I have ran in to another issue with my protection that I can't figure out.
Once I put my protection on my cells with formulas...which I now have with my
macros perfectly... my formulas will not automatically update based on the
entries in the other cells that are inclucded in the formula. I actually have
to save the workbook before my changes show in my formula. Is there something
that I am missing?

Thanks for the help!

 

[Gord Dibben]

The missing part is that you have calculation set to Manual/Calc on Save.

Change it back to Automatic.

Tools>Options>Calculation or in 2007

Orb>Excel Options>Advanced>Formulas......uncheck Manual


Gord