protecting multiple worksheets but not the entire workbook

rdw29

New Member
Joined
Jul 10, 2002
Messages
2
I am in Excel 97 trying to protect multiple worksheets with the same password. Problem is, I don't know how to do it without protecting each sheet individually. I have tried a macro, but the program will not apply the password (still locks the sheet) therefore, it leaves the sheets able to be unlocked by anyone since the pw is not specified in the macro. Any answers?

Thanks!
This message was edited by rdw29 on 2002-07-11 11:27
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Select the sheets you wanted protected.

Then run this macro:

<pre><font color='#000000'>
<font color='#000080'>Sub</font> ProtectMulti()

<font color='#000080'>Dim</font> wks <font color='#000080'>As</font> Worksheet
<font color='#000080'>Dim</font> arrSheets() <font color='#000080'>As</font> <font color='#000080'>String</font>
<font color='#000080'>Dim</font> intI <font color='#000080'>As</font> Integer, intJ <font color='#000080'>As</font> <font color='#000080'>Integer</font>

<font color='#008000'>' Read the currently selected sheet names</font>
<font color='#008000'>' into an array</font>
<font color='#000080'>For</font> <font color='#000080'>Each</font> wks In ActiveWindow.SelectedSheets
intI = intI + 1
<font color='#000080'>ReDim</font> <font color='#000080'>Preserve</font> arrSheets(intI)
arrSheets(intI) = wks.Name
<font color='#000080'>Next</font> wks

<font color='#008000'>' This part will un-select the sheets so that we can</font>
<font color='#008000'>' protect the ones we want.</font>
<font color='#000080'>For</font> <font color='#000080'>Each</font> wks In ThisWorkbook.Worksheets
wks.Select
<font color='#000080'>Next</font> wks

<font color='#008000'>' Now protect the (previously) selected sheets</font>
<font color='#000080'>For</font> intJ = 1 To intI
ThisWorkbook.Worksheets(arrSheets(intJ)).Protect "MyPass"
<font color='#000080'>Next</font> intJ

<font color='#000080'>End</font> <font color='#000080'>Sub</font>

</font></pre>

It worked for me - it should for you too!

-Russell
 
Upvote 0
On 2002-07-11 12:27, Russell Hauf wrote:
Select the sheets you wanted protected.

Then run this macro:

<pre><font color='#000000'>
<font color='#000080'>Sub</font> ProtectMulti()

<font color='#000080'>Dim</font> wks <font color='#000080'>As</font> Worksheet
<font color='#000080'>Dim</font> arrSheets() <font color='#000080'>As</font> <font color='#000080'>String</font>
<font color='#000080'>Dim</font> intI <font color='#000080'>As</font> Integer, intJ <font color='#000080'>As</font> <font color='#000080'>Integer</font>

<font color='#008000'>' Read the currently selected sheet names</font>
<font color='#008000'>' into an array</font>
<font color='#000080'>For</font> <font color='#000080'>Each</font> wks In ActiveWindow.SelectedSheets
intI = intI + 1
<font color='#000080'>ReDim</font> <font color='#000080'>Preserve</font> arrSheets(intI)
arrSheets(intI) = wks.Name
<font color='#000080'>Next</font> wks

<font color='#008000'>' This part will un-select the sheets so that we can</font>
<font color='#008000'>' protect the ones we want.</font>
<font color='#000080'>For</font> <font color='#000080'>Each</font> wks In ThisWorkbook.Worksheets
wks.Select
<font color='#000080'>Next</font> wks

<font color='#008000'>' Now protect the (previously) selected sheets</font>
<font color='#000080'>For</font> intJ = 1 To intI
ThisWorkbook.Worksheets(arrSheets(intJ)).Protect "MyPass"
<font color='#000080'>Next</font> intJ

<font color='#000080'>End</font> <font color='#000080'>Sub</font>

</font></pre>

It worked for me - it should for you too!

-Russell

HI Russell,

how did you colour your VBA text on this board ?

(other than manually)

it's a nice touch.... please don't tell me you copy pasted each colour command into the HTML

cheers
Chris
:)
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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
Back
Top