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!

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
---------------
Jacob Skaria


"ladytiger7481" wrote:

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!

I think I am going about this all wrong. What I need my macro to do is
protect two seperate ranges of cells say D8:D43 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!!

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"ladytiger7481" wrote:

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!

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


Gord Dibben MS Excel MVP

On Wed, 19 Aug 2009 14:19:01 -0700, ladytiger7481
wrote:

I think I am going about this all wrong. What I need my macro to do is
protect two seperate ranges of cells say D8:D43 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!!

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"ladytiger7481" wrote:

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!

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 ToolsVBAProject
PropertiesProtection

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


Gord

On Wed, 19 Aug 2009 14:34:36 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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


Gord Dibben MS Excel MVP

On Wed, 19 Aug 2009 14:19:01 -0700, ladytiger7481
wrote:

I think I am going about this all wrong. What I need my macro to do is
protect two seperate ranges of cells say D8:D43 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!!

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"ladytiger7481" wrote:

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!

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" wrote:

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 ToolsVBAProject
PropertiesProtection

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


Gord

On Wed, 19 Aug 2009 14:34:36 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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


Gord Dibben MS Excel MVP

On Wed, 19 Aug 2009 14:19:01 -0700, ladytiger7481
wrote:

I think I am going about this all wrong. What I need my macro to do is
protect two seperate ranges of cells say D8:D43 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!!

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"ladytiger7481" wrote:

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!

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

Change it back to Automatic.

ToolsOptionsCalculation or in 2007

OrbExcel OptionsAdvancedFormulas......uncheck Manual


Gord

On Wed, 19 Aug 2009 21:15:01 -0700, ladytiger7481
wrote:

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" wrote:

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 ToolsVBAProject
PropertiesProtection

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


Gord

On Wed, 19 Aug 2009 14:34:36 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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


Gord Dibben MS Excel MVP

On Wed, 19 Aug 2009 14:19:01 -0700, ladytiger7481
wrote:

I think I am going about this all wrong. What I need my macro to do is
protect two seperate ranges of cells say D8:D43 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!!

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"ladytiger7481" wrote:

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!