Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Cas
 
Posts: n/a
Default Protect multiple worksheets

Hi Folks

I have a workbook consisting of 14 worksheets. This requires users to enter
data in various cells but I dont wish them to have access to all cells.
Therefore I use Excels "Protect sheet...." feature to prevent users
modifying formulas etc. The workbook periodically has to be changed and this
means individually unprotecting each worksheet and then re-protecting them.
Is there a way to select all 14 sheets and then protect/unprotect in one hit
?

This would save me a great deal of hassel (entering the password 28 times!)
so any solution would be very welcome.

TIA

Cas.


  #2   Report Post  
Harald Staff
 
Posts: n/a
Default

Hi Cas

Two very useful macros for this:

Sub LockAll()
Dim wks As Worksheet
Dim Pw As String
Dim i As Long
i = 0
Pw = InputBox("Password:", "Lock all worksheets")
If StrPtr(Pw) = 0 Then Exit Sub
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
wks.Protect Pw
If Err.Number < 0 Then
i = i + 1
Err.Clear
End If
Next
If i 0 Then MsgBox i & _
" errors during this operation"
End Sub

Sub UnLockAll()
Dim wks As Worksheet
Dim Pw As String
Dim i As Long
i = 0
Pw = InputBox("Password:", "Lock all worksheets")
If StrPtr(Pw) = 0 Then Exit Sub
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
wks.Unprotect Pw
If Err.Number < 0 Then
i = i + 1
Err.Clear
End If
Next
If i 0 Then MsgBox i & _
" errors during this operation"
End Sub

HTH. Best wishes Harald

"Cas" skrev i melding
...
Hi Folks

I have a workbook consisting of 14 worksheets. This requires users to
enter data in various cells but I dont wish them to have access to all
cells. Therefore I use Excels "Protect sheet...." feature to prevent users
modifying formulas etc. The workbook periodically has to be changed and
this means individually unprotecting each worksheet and then re-protecting
them. Is there a way to select all 14 sheets and then protect/unprotect in
one hit ?

This would save me a great deal of hassel (entering the password 28
times!) so any solution would be very welcome.

TIA

Cas.



  #3   Report Post  
Anne Troy
 
Posts: n/a
Default

You might want to try something like this:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=33

But this will protect/unprotect all sheets at once:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=142

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Cas" wrote in message
...
Hi Folks

I have a workbook consisting of 14 worksheets. This requires users to

enter
data in various cells but I dont wish them to have access to all cells.
Therefore I use Excels "Protect sheet...." feature to prevent users
modifying formulas etc. The workbook periodically has to be changed and

this
means individually unprotecting each worksheet and then re-protecting

them.
Is there a way to select all 14 sheets and then protect/unprotect in one

hit
?

This would save me a great deal of hassel (entering the password 28

times!)
so any solution would be very welcome.

TIA

Cas.




  #4   Report Post  
Cas
 
Posts: n/a
Default

Many thanks to all who responded.
For those with a similar need -

http://www.vbaexpress.com/kb/getarticle.php?kb_id=142

Worked a treat. (I'm sure the others are fine also but this was the first
one I tried!)

Many thanks again.

"Anne Troy" wrote in message
news:46e35$42bbcab8$97c5108d$5677@allthenewsgroups .com...
You might want to try something like this:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=33

But this will protect/unprotect all sheets at once:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=142

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Cas" wrote in message
...
Hi Folks

I have a workbook consisting of 14 worksheets. This requires users to

enter
data in various cells but I dont wish them to have access to all cells.
Therefore I use Excels "Protect sheet...." feature to prevent users
modifying formulas etc. The workbook periodically has to be changed and

this
means individually unprotecting each worksheet and then re-protecting

them.
Is there a way to select all 14 sheets and then protect/unprotect in one

hit
?

This would save me a great deal of hassel (entering the password 28

times!)
so any solution would be very welcome.

TIA

Cas.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Protect multiple worksheets Cas Excel Discussion (Misc queries) 3 June 24th 05 03:39 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM
Extracting data from multiple worksheets into a list mnirula Excel Worksheet Functions 16 February 25th 05 09:52 PM
How to protect and unprotect 30 worksheets in a file every month . Protect & Unprotect Several Worksheets Excel Worksheet Functions 4 January 10th 05 02:29 PM
Countif with multiple criteria and multiple worksheets JJ Excel Worksheet Functions 1 December 28th 04 07:37 PM


All times are GMT +1. The time now is 04:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"