Excel protecting multiple worksheets but not the entire workbook :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

protecting multiple worksheets but not the entire workbook
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

rdw29
Welcome to the Board


Joined: 11 Jul 2002
Posts: 2


Status: Offline

 Reply with quote  

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 ]

Post Thu Jul 11, 2002 3:08 pm 
 View user's profile Send private message

Russell Hauf
MrExcel MVP


Joined: 11 Feb 2002
Posts: 1205
Location: Portland, OR USA
Flag: Usa

Status: Offline

 Reply with quote  

Select the sheets you wanted protected.

Then run this macro:



Sub ProtectMulti()

Dim wks As Worksheet
Dim arrSheets() As String
Dim intI As Integer, intJ As Integer

' Read the currently selected sheet names
' into an array
For Each wks In ActiveWindow.SelectedSheets
intI = intI + 1
ReDim Preserve arrSheets(intI)
arrSheets(intI) = wks.Name
Next wks

' This part will un-select the sheets so that we can
' protect the ones we want.
For Each wks In ThisWorkbook.Worksheets
wks.Select
Next wks

' Now protect the (previously) selected sheets
For intJ = 1 To intI
ThisWorkbook.Worksheets(arrSheets(intJ)).Protect "MyPass"
Next intJ

End Sub



It worked for me - it should for you too!

-Russell

Post Thu Jul 11, 2002 4:27 pm 
 View user's profile Send private message Send e-mail Visit poster's website

Chris Davison
MrExcel MVP


Joined: 16 Feb 2002
Posts: 1715
Location: Millbank, London, UK

Status: Offline

 Reply with quote  


quote:

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

Then run this macro:



Sub ProtectMulti()

Dim wks As Worksheet
Dim arrSheets() As String
Dim intI As Integer, intJ As Integer

' Read the currently selected sheet names
' into an array
For Each wks In ActiveWindow.SelectedSheets
intI = intI + 1
ReDim Preserve arrSheets(intI)
arrSheets(intI) = wks.Name
Next wks

' This part will un-select the sheets so that we can
' protect the ones we want.
For Each wks In ThisWorkbook.Worksheets
wks.Select
Next wks

' Now protect the (previously) selected sheets
For intJ = 1 To intI
ThisWorkbook.Worksheets(arrSheets(intJ)).Protect "MyPass"
Next intJ

End Sub



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
icon_smile.gif

Post Thu Jul 11, 2002 5:42 pm 
 View user's profile Send private message Send e-mail ICQ Number
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.