VBA to protect and unprotect Sheets

VBA to protect and unprotect Sheets

Protecting and unprotecting sheets is a common action for an Excel user. There is nothing worse than when somebody, who doesn’t know what they’re doing, over types essential formulas and cell values. It’s even worse when that person happens to be ourselves; all it takes is one accidental keypress, and suddenly the entire worksheet is filled with errors. In this post, we explore using VBA to protect and unprotect sheets.

Adapting the code for your purposes

Unless stated otherwise, every example below is based on one specific worksheet. Each code includes Sheets(“Sheet1”)., this means the action will be applied to that specific sheet. For example, the following protects Sheet1.

Example

Sub ProtectSheet()
ActiveSheet.Protect
End Sub

Applying a sheet to a variable

Sub ProtectSheet()
Dim WSheet As Worksheet
Set WSheet = Sheets("Sheet1")
WSheet.Protect
End Sub

Protect and unprotect:

Example

Protect Sheet Without a Password

Sub ProtectSheet() Sheets(“Sheet1”).Protect End Sub

Example

UnProtect a sheet no Password

Sub UnProtectSheet()
Sheets(“Sheet1”).UnProtect
End Sub

Protecting and unprotecting with a password

Adding a password to give an extra layer of protection is easy enough with VBA. The password in these examples is hardcoded into the macro; this may not be the best for your scenario. It may be better to apply using a string variable, or capturing user passwords with an InputBox.

Example

Protecting Sheet with Password

Sub ProtectSheetpassword()
Sheets(“Sheet1”).Protect Password:=”123”
End Sub

Example

UnProtecting Sheet with Password

Sub ProtectSheetpassword()
Sheets(“Sheet1”).UnProtect Password:=”123”
End Sub

Please watching My Video is Below

Design by Vannak Vannaro