Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protection macro
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protection macro
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 "Neil Pearce" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protection macro
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 "Mike H" wrote: 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 "Neil Pearce" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protection macro
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) On Thu, 23 Oct 2008 10:26:09 -0700, Neil Pearce wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protection macro
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 "Chip Pearson" wrote: 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) On Thu, 23 Oct 2008 10:26:09 -0700, Neil Pearce wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
password protection macro - HELP???? | Excel Discussion (Misc queries) | |||
Macro and row protection | Excel Discussion (Misc queries) | |||
Protection Macro | Excel Discussion (Misc queries) | |||
Macro Protection | New Users to Excel | |||
macro for protection | Excel Discussion (Misc queries) |