Is there a way to password protect multiple sheets in a workbook without have
to protect them individually. Not all the sheets in the workbook will need
to be protected, just 15 or so...thanks

Sub Protect_Selected_Sheets()
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.Protect Password:="justme"
Next ws
End Sub

Pre-select the 15 or so sheets using CTRL + Click for non-contiguous or
SHIFT + Click for contiguous.


Gord Dibben MS Excel MVP

On Wed, 11 Mar 2009 10:47:03 -0700, Rover
wrote:

Is there a way to password protect multiple sheets in a workbook without have
to protect them individually. Not all the sheets in the workbook will need
to be protected, just 15 or so...thanks

I amassuming I just type in what you wrote in a macro? Also, how would you
unprotect the same 15 or so sheets at the same time?

"Gord Dibben" wrote:

Sub Protect_Selected_Sheets()
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.Protect Password:="justme"
Next ws
End Sub

Pre-select the 15 or so sheets using CTRL + Click for non-contiguous or
SHIFT + Click for contiguous.


Gord Dibben MS Excel MVP

On Wed, 11 Mar 2009 10:47:03 -0700, Rover
wrote:

Is there a way to password protect multiple sheets in a workbook without have
to protect them individually. Not all the sheets in the workbook will need
to be protected, just 15 or so...thanks

I would copy the macro I posted to a general module in your workbook or
Personal.xls.

Make a copy of the macro and change ws.Protect to ws.UnProtect in the
second copy.

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord


On Wed, 11 Mar 2009 14:26:04 -0700, Rover
wrote:

I amassuming I just type in what you wrote in a macro? Also, how would you
unprotect the same 15 or so sheets at the same time?

"Gord Dibben" wrote:

Sub Protect_Selected_Sheets()
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.Protect Password:="justme"
Next ws
End Sub

Pre-select the 15 or so sheets using CTRL + Click for non-contiguous or
SHIFT + Click for contiguous.


Gord Dibben MS Excel MVP

On Wed, 11 Mar 2009 10:47:03 -0700, Rover
wrote:

Is there a way to password protect multiple sheets in a workbook without have
to protect them individually. Not all the sheets in the workbook will need
to be protected, just 15 or so...thanks

Add a user form,, in the user form ,, add following code in command
button click event

Code:
--------------------

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 15
Worksheets(i).Protect "a"
Next
End Sub

--------------------




Chris
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com




--
Chris Bode

THANK YOU...you guys are awesome and very helpful...I managed to write my
first macro and its actually works...THANK YOU again...

"Rover" wrote:

Is there a way to password protect multiple sheets in a workbook without have
to protect them individually. Not all the sheets in the workbook will need
to be protected, just 15 or so...thanks

Gord,

The macro did work, however, it protected all 36 sheets of my workbook. I
just want to protect sheets 21 thru 36 (the sheets I want to protect are in
consecutive order). I have tried to make it work, but cant seem to.
Can you please help again?

"Gord Dibben" wrote:

I would copy the macro I posted to a general module in your workbook or
Personal.xls.

Make a copy of the macro and change ws.Protect to ws.UnProtect in the
second copy.

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord


On Wed, 11 Mar 2009 14:26:04 -0700, Rover
wrote:

I amassuming I just type in what you wrote in a macro? Also, how would you
unprotect the same 15 or so sheets at the same time?

"Gord Dibben" wrote:

Sub Protect_Selected_Sheets()
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.Protect Password:="justme"
Next ws
End Sub

Pre-select the 15 or so sheets using CTRL + Click for non-contiguous or
SHIFT + Click for contiguous.


Gord Dibben MS Excel MVP

On Wed, 11 Mar 2009 10:47:03 -0700, Rover
wrote:

Is there a way to password protect multiple sheets in a workbook without have
to protect them individually. Not all the sheets in the workbook will need
to be protected, just 15 or so...thanks

The macro will protect as many sheets as you have pre-selected using CTRL +
click or Shift + click.

Sounds like you selected all 36 sheets.


Gord

On Thu, 12 Mar 2009 13:34:01 -0700, Rover
wrote:

Gord,

The macro did work, however, it protected all 36 sheets of my workbook. I
just want to protect sheets 21 thru 36 (the sheets I want to protect are in
consecutive order). I have tried to make it work, but cant seem to.
Can you please help again?

"Gord Dibben" wrote:

I would copy the macro I posted to a general module in your workbook or
Personal.xls.

Make a copy of the macro and change ws.Protect to ws.UnProtect in the
second copy.

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord


On Wed, 11 Mar 2009 14:26:04 -0700, Rover
wrote:

I amassuming I just type in what you wrote in a macro? Also, how would you
unprotect the same 15 or so sheets at the same time?

"Gord Dibben" wrote:

Sub Protect_Selected_Sheets()
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.Protect Password:="justme"
Next ws
End Sub

Pre-select the 15 or so sheets using CTRL + Click for non-contiguous or
SHIFT + Click for contiguous.


Gord Dibben MS Excel MVP

On Wed, 11 Mar 2009 10:47:03 -0700, Rover
wrote:

Is there a way to password protect multiple sheets in a workbook without have
to protect them individually. Not all the sheets in the workbook will need
to be protected, just 15 or so...thanks