Protect And Unprotect Sheets Macro

Home/VBA – Macros/ Protect And Unprotect Sheets Macro

Protect and unprotect sheets macro

I frequently protect sheets without using a password to ensure users don’t accidentally change something they shouldn’t. Is there an easy way to unprotect all the sheets in a workbook and then protect them again?

By Neale Blackwood

There is no built-in method to protect or unprotect all the sheets at once, but you can use some simple macros to make the job easier. These all assume no password is used. Note: chart sheets will not be affected by these macros.

The following code will protect all the sheets in a workbook

Sub ProtectAllSheets()
Dim sht As Worksheet
For Each sht In Worksheets
sht.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True
Next sht
End Sub

The following code will unprotect all the sheets in a workbook

Sub UnprotectAllSheets()
Dim sht As Worksheet
For Each sht In Worksheets
sht.Unprotect
Next sht
End Sub

To protect the active (current) sheet use

Sub ProtectSheet()
ActiveSheet.Protect DrawingObjects:=-True, Contents:=True, Scenarios:=True
End Sub

To unprotect the active (current) sheet use

Sub UnprotectSheet()
ActiveSheet.Unprotect
End Sub

2017-03-08T03:53:58+00:00 VBA – Macros|