Protection macro

[Neil Pearce]

Is there a macro that will automatically unprotect all worksheets when any
other marco is run?

More porblematic one would assume is a macro that automatically protects all
worksheets when another macro ends?

 

[Mike H]

You would have to write one.

This protects all sheets if they are unprotected and vice versa. Simply call
it from your sub

Sub sistence()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.ProtectContents Then
ws.Unprotect Password:="MyPass"
Else
ws.Protect Password:="MyPass"
End If
Next
End Sub

Mike

 

[Chip Pearson]

You might consider protecting the sheets using the UserInterfaceOnly
parameter. This prevents the user from modifying the worksheet but
allows VBA to do anything it wants, regardless of protection. This
property does not stick with the worksheet when the file is closed, so
you should use something like the following, in the ThisWorkbook
module:

Private Sub Workbook_Open()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
WS.Protect UserInterfaceOnly:=True
Next WS
End Sub

This will protect all the sheets from user modification but will allow
your VBA code to run without protection problems.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Neil Pearce]

Thanks Chip,

This works really well, however the macro below if affected by protection
still. Any ideas?

Also the user can uprotect the cells via the tools -> protection option. Is
it possible to add a password to your code below to prevent this?

Sub Refresh()
'
' Refresh Macro
' Macro recorded 20/10/2008 by Gleeds User
'

'
Range("A3").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
Range("D3").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Range("G3").Select
ActiveSheet.PivotTables("PivotTable18").PivotCache.Refresh
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

End Sub


Thanking-you,

Neil

 

[Neil Pearce]

Thanks Mike, That's brilliant! And much quicker putting the below before
each and every code, thank-you.

Sheets("Drainage Accessories Input").Select
ActiveSheet.Unprotect
Sheets("Drainage Input").Select
ActiveSheet.Unprotect
Sheets("Accessories").Select
ActiveSheet.Unprotect
Sheets("Summary Tables").Select
ActiveSheet.Unprotect

Sheets("Drainage Accessories Input").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Drainage Input").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Accessories").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Summary Tables").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True