Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macros for Protect/Unprotect all sheets in a workbook
Macros for Protect/Unprotect all sheets in a workbook -------------------------------------------------------------------------------- I have been using the macros below for Protecting/Unprotecting all sheets in a workbook, they work ok unless the sheets are password protected, in which case the Unprotect drop down appears and I have to enter the password for each sheet Is there any way to change the macros so that irrespective of how many sheets there are I only have to enter the password once to either unprotect them all or protect them all If possible the macro should still run for sheets with no password Sub unprotect_all() Dim wks As Worksheet On Error Resume Next For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Next End Sub Sub protect_all() Dim wks As Worksheet On Error Resume Next For Each wks In ActiveWorkbook.Worksheets wks.Protect Next End Sub -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=392836 |
#2
|
|||
|
|||
One way:
Public Sub Protect_All() Dim wks As Worksheet Dim vPword As Variant vPword = Application.InputBox( _ Prompt:="Enter Password: ", _ Title:="Protect sheets", _ Default:="", _ Type:=2) If vPword = False Then Exit Sub 'user cancelled For Each wks In ActiveWorkbook.Worksheets wks.Protect vPword Next End Sub Public Sub Unprotect_All() Dim wks As Worksheet Dim vPword As Variant On Error Resume Next For Each wks In ActiveWorkbook.Worksheets With wks .Unprotect vPword Do While .ProtectContents vPword = Application.InputBox( _ Prompt:="Enter password for " & .Name, _ Title:="Unprotect sheets", _ Default:="", _ Type:=2) If vPword = False Then Exit Sub 'user cancelled .Unprotect vPword Loop End With Next End Sub Note: Unprotect_All will allow you to unprotect all sheets, even if there are different (or no) passwords on each sheet. In article , Paul Sheppard wrote: Macros for Protect/Unprotect all sheets in a workbook ------------------------------------------------------------------------------ -- I have been using the macros below for Protecting/Unprotecting all sheets in a workbook, they work ok unless the sheets are password protected, in which case the Unprotect drop down appears and I have to enter the password for each sheet Is there any way to change the macros so that irrespective of how many sheets there are I only have to enter the password once to either unprotect them all or protect them all If possible the macro should still run for sheets with no password Sub unprotect_all() Dim wks As Worksheet On Error Resume Next For Each wks In ActiveWorkbook.Worksheets wks.Unprotect Next End Sub Sub protect_all() Dim wks As Worksheet On Error Resume Next For Each wks In ActiveWorkbook.Worksheets wks.Protect Next End Sub |
#3
|
|||
|
|||
Thanks, thats really useful -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=392836 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect macros? | Excel Discussion (Misc queries) | |||
Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
Enabling macros | Excel Discussion (Misc queries) | |||
Multiple sheets selected | Excel Discussion (Misc queries) |