Protect Selected Workbooks Excel VBA Macro

In this post we learn how to protect all selected workbooks in Excel using VBA macros. The macro protects the workbook structure and the contents of each worksheet with a password. Alternatively, or additionally, another line of code can be added to encrypt the file with that password. The macro initially asks the user to set a password, and then displays a dialog to select the files. The same code can easily be adapted to unprotect the workbooks with a password.

Macro/VBA code:

 

  Sub ProtectSelectedWorkbooks()

      Dim psw As String, selFiles As Variant, file As Variant, ws As Worksheet

 

      'Set password

      psw = InputBox("Enter password to protect workbooks")

 

      If psw <> "" Then

          'Open dialog to select files

          selFiles = Application.GetOpenFilename(Filefilter:= _

 "Excel Files (*.xls*),*.xls*", Title:="Select Files", MultiSelect:=True)

 

 Application.ScreenUpdating = False

 For Each file In selFiles

               Workbooks.Open file

               With ActiveWorkbook

              'Protect sheets with password

              For Each ws In .Sheets

                  ws.Protect psw

              Next ws

              .Protect Password:=psw  'Protect workbook structure with password

              .Password = psw  'Encrypt workbook with password (optional)   

      .Save

              .Close

              End With

 Next file

 Application.ScreenUpdating = True

      Else

           MsgBox "No password set"

      End If

  End Sub

 

Macro explained:

• First we declare a string variable to keep the password (psw), two variant variables to store the array of selected files and each individual file (selFiles and file), and a Worksheet object variable (ws) to be used while looping through each worksheet.
• An input box is used to request the password that is going to be used to protect all workbooks and worksheets. A conditional statement is added to confirm a password has been entered.
• We use the GetOpenFilename method of the Application object to display a dialog box to select the files, specifying the type as an Excel file only, setting the title of the dialog, and allowing to select multiple files.
• Then we loop through each file selected, previously setting the screen update to False in order to just open the files in the background. After opening each file, the macro takes the following actions:
• Loops and protects each worksheet in the workbook with the given password. Note that the expression protects just everything in the worksheet, but we can also specify to protect certain components only, for example, allowing to change format, or sort/filter rows, etc. Alternatively, certain cells, or columns/rows, can be unlocked before protecting the worksheet, thus allowing user to add or modify the values in those cells.
• Protects the structure of each workbook with a password. When protected, sheets cannot be added, moved, or deleted, for that workbook.
• Alternatively, or additionally, it protects or encrypts the entire file with a password, which causes the file to prompt a password request upon opening. The contents are not shown until the correct password is entered.
• Finally, it saves and closes the workbook.
  

• The screen updating is turned on again after looping through each workbook.
• A conditional statement was added to check whether a password was entered, otherwise it shows an alert and does not proceed with the macro.

 

This is how we protect all selected workbooks in Excel with VBA macros.

Other examples:

• How to Import Data From Google Sheets with Excel VBA macros
  
• How to Show Chart For Selected Row with Excel VBA macros
  
• How to Sort and Merge Groups In Table with Excel VBA macros