Copy Code:

Private Sub CommandButton1_Click()
'To protect active sheet
ActiveSheet.Protect "HiHowAreYou"
End Sub

Copy Code:

Private Sub CommandButton2_Click()
'To Unprotect Active sheet
ActiveSheet.Unprotect "HiHowAreYou"
End Sub

2)Protect WorkBook

Copy Code to protect workbook:

Private Sub CommandButton1_Click()
'Syntax:
'ThisWorkbook.Protect Password:="password", structure:=True
ThisWorkbook.Protect Password:="IamPassword", structure:=True
End Sub

Copy Code to unprotect workbook:

Private Sub CommandButton2_Click()
'Syntax:
'ThisWorkbook.Unprotect Password:="password"
ThisWorkbook.Unprotect Password:="IamPassword"
End Sub

3)Protect all sheets FOR EACH LOOP

Copy code:Protect all sheets through FOR EACH LOOP

Private Sub CommandButton1_Click()
'defining worksheet object
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Protect Password:="Hipwd"
Next
ThisWorkbook.Protect Password:="IamPassword", structure:=True
MsgBox "All sheets in this workbook are protected"
End Sub

Copy code:UnProtect all sheets through FOR EACH LOOP:

Private Sub CommandButton2_Click()
'defining worksheet object
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Unprotect Password:="Hipwd"
Next
ThisWorkbook.Unprotect Password:="IamPassword"
MsgBox "All sheets in this workbook are unprotected"
End Sub

4)Protect all sheets FOR LOOP

Copy code:Protect all sheets through FOR LOOP

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To Sheets.Count
Sheets(i).Protect Password:="hello"
Next
ThisWorkbook.Protect Password:="IamPassword", structure:=True
MsgBox "All sheets in this workbook are protected"
End Sub

Copy code:UnProtect all sheets through FOR LOOP

Private Sub CommandButton2_Click()
Dim i As Integer
For i = 1 To Sheets.Count
Sheets(i).Unprotect Password:="hello"
Next
ThisWorkbook.Unprotect Password:="IamPassword"
MsgBox "All sheets in this workbook are Unprotected"
End Sub

5)Assign different passwords for different sheets

Copy code to protect all the sheets with different passwords:

Private Sub CommandButton1_Click()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Sheets
If sh.Name = "sheet6" Then
sh.Protect Password:="sheet6"
ElseIf sh.Name = "sheet2" Then
sh.Protect Password:="sheet2"
Else
sh.Protect Password:="hello"
End If
Next
ThisWorkbook.Protect Password:="IamPassword", structure:=True
MsgBox "sheets are protected"
End Sub

Copy code to Unprotect all the sheets with different passwords:

Private Sub CommandButton2_Click()
For Each sh In ThisWorkbook.Sheets
If sh.Name = "sheet6" Then
sh.Unprotect Password:="sheet6"
ElseIf sh.Name = "sheet2" Then
sh.Unprotect Password:="sheet2"
Else
sh.Unprotect Password:="hello"
End If
Next
ThisWorkbook.Unprotect Password:="IamPassword"
MsgBox "sheets are unprotected"
End Sub

6)Protect all sheets by assigining sheet name itself as pwd

Copy code:Protect all sheets through FOR LOOP

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To Sheets.Count
'password is sheet name itself
Sheets(i).Protect Password:=Sheets(i).Name
Next
MsgBox "hello sheets are protected"
End Sub

Copy code:UnProtect all sheets through FOR LOOP

Private Sub CommandButton2_Click()
Dim i As Integer
For i = 1 To Sheets.Count
'password is sheet name itself
Sheets(i).Unprotect Password:=Sheets(i).Name
Next
MsgBox "hello sheets are Unprotected"
End Sub

7)To display protect dialoge box

Enter the password in displayed dialogue box

Private Sub CommandButton1_Click()
'todisplay protect dialogue box
Application.Dialogs(xlDialogProtectDocument).Show
End Sub

8)Protect entire sheet except required cells

Through this program we can keep required cells in edit mode and lock entire sheet

Code:

Private Sub CommandButton1_Click()
'ThisWorkbook.Sheets("Sheet2").Range("A1").Locked = False
'ThisWorkbook.Sheets("Sheet2").Cells(1, 1).Locked = False
Range("A1").Value = "UnProtected"
Range("A1").Locked = False
Cells(6, 7).Value = "UnProtected"
Cells(6, 7).Locked = False
Range("A5:C8").Value = "edit"
Range("A5:C8").Locked = False
ActiveSheet.Protect Password:="abc"
End Sub

9)Protect alternate cells

Code:

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 11
If Cells(i, 1).Row Mod 2 = 0 Then
'Excel Formula:=IF(MOD(ROW(),2) = 0,"Protect","Unprotect")
Cells(i, 1) = "Protected"
Cells(i, 1).Locked = True
Else
Cells(i, 1) = "Unprotected"
Cells(i, 1).Locked = False
End If
Next
ActiveSheet.Protect Password:="abc"
End Sub

Unprotect sheet and Clear data:

Private Sub CommandButton2_Click()
ActiveSheet.Unprotect "abc"
Range("A1:A11").Clear
End Sub

Result:

10)Protect required cells and UNPROTECT remaining sheet

Code:

Private Sub CommandButton1_Click()
ActiveSheet.Cells.Select
Selection.Locked = False
Range("A1:A14").Select
Selection.Locked = True
Range("B1:J1").Select
Selection.Locked = True
'Selection.SpecialCells(xlCellTypeBlanks).Select
ActiveSheet.Protect "abc"
End Sub

ActiveSheet.Cells.Select

Selection.Locked = False

First free\unprotect entire sheet cells

Range("A1:A14").Select

Lock cells,to protect row data

Range("B1:J1").Select

Lock cells to protect column data