How do I password protect a document using passwords in VBA?

Hi Ed

The code below show you how to password protect multiple worksheets in a workbook. It can easily be adapted to cover all needs

Protecting all sheets with the same password for each sheet

Sub ProtectAllMYSheets()
'This protects all the worksheets with the same password.
'If the worksheet is protected it unprotects it
'You could make this two separate procedures by removing the IF statement; One to protect the other to Unprotect

Dim SheetVar As Worksheet

For Each SheetVar In ActiveWorkbook.Worksheets

If SheetVar.ProtectContents = False Then

SheetVar.Protect Password:="Apple", UserInterfaceOnly:=True

Else

SheetVar.Unprotect Password:="Apple"

End If

Next SheetVar

End Sub


Protecting all sheets with a different password for each sheet

Sub PasswordProtectAllSheets()
'Protect all sheets with a separate password for each
'Again as above: If the worksheet is protected it unprotects it
'You could make this two separate procedures by removing the IF statement; One to protect the other to Unprotect

Dim SheetVar As Worksheet
Dim strPassword As String

For Each SheetVar In ActiveWorkbook.Worksheets

Select Case UCase(SheetVar.CodeName)

Case "SHEET1": strPassword = "Carrot"
Case "SHEET2": strPassword = "Tomato"
Case "SHEET3": strPassword = "Onion"

'Follow pattern :One password for each sheet
'Remember that CodeName always refers to the name the system gives the sheets
'eg Sheet1, Sheet2, etc

'If you have renamed the sheets then replace the reference to Sheet1, etc.
'with the new name and "SheetVar.Name" in the bracket

Case Else: strPassword = "Salad"

End Select


If SheetVar.ProtectContents = False Then

SheetVar.Protect Password:=strPassword, UserInterfaceOnly:=True

Else

SheetVar.Unprotect Password:=strPassword

End If

Next SheetVar

End Sub

Hope this helps you all

Carlos