How to protect all workbooks in a folder at once in Excel?
In Excel, you can protect a workbook to prevent other users from editing it, but have you ever imaged to protect multiple workbooks at once? Here I introduce some tricks on protecting all workbooks in a folder and multiple sheets in a workbook in Excel.
Protect workbooks in a folder at once with VBA
Protect multiple sheets at once with Kutools for Excel
Protect workbooks in a folder at once with VBA
To protect workbooks in a specific folder, you can apply a macro code to solve.
1. Enable a new workbook, and press Alt + F11 keys to open the Microsoft Visual Basic for Applications window, and click Insert > Module, and paste below code to the Module script.
VBA: Protect workbooks in a folder.
Sub ProtectAll()
Dim xWorkBooks As Workbook
Dim xExitFile As String
Dim xPassWord As Variant
Dim xStrPath As String
Dim xFileDialog As FileDialog
Dim xFile As String
On Error Resume Next
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = False
xFileDialog.Title = "Select a folder [Kutools for Excel]"
If xFileDialog.Show = -1 Then
xStrPath = xFileDialog.SelectedItems(1)
End If
If xStrPath = "" Then
Exit Sub
Else
xStrPath = xStrPath + "\"
End If
xPassWord = Application.InputBox("Enter password", "Kutools for Excel", , , , , , 2)
If (xPassWord = False) Or (xPassWord = "") Then
MsgBox "Password cannot be blank!", vbInformation, "Kutools for Excel"
Exit Sub
End If
xFile = "*.xls"
xExitFile = Dir(xStrPath & xFile)
On Error Resume Next
Application.ScreenUpdating = False
Do While xExitFile <> ""
Set xWorkBooks = Workbooks.Open(xStrPath & xExitFile)
Application.DisplayAlerts = False
xWorkBooks.SaveAs Filename:=xWorkBooks.FullName, Password:=xPassWord
Application.DisplayAlerts = True
Workbooks(xExitFile).Close False
Set xWorkBooks = Nothing
xExitFile = Dir
Loop
Application.ScreenUpdating = True
MsgBox "Successfully protect!", vbInformation, "Kutools for Excel"
End Sub
2. Press F5 key, and a Select a folder window pops out for you to select a folder to protect its all worksheets.
3. Click OK and a dialog pops out for entering password for protecting workbooks.
4. Click OK, a dialog pops out to remind you all workbooks in the selected folder have been successfully protected with one password.
When you open the workbooks you protected by above code, a dialog pops out for entering password. See screenshot:
Tip: This code works for 2007 or higher Excel version, if you want to apply it to protect lower version, you can change xFile = "*.xls" to xFile = "*.xlsx" in the code.
Protect multiple sheets at once with Kutools for Excel
Actually, it will be more frequent to protect multiple sheets in a workbook in our daily working. But in Excel, you need to protect sheets one by one with its built-in function. However, there is a powerful tool ā Kutools for Excel, its Protect Worksheet utility can help you protect all sheets or selected sheets of the workbook at once.
After free installing Kutools for Excel, please do as below:
1. Enable the workbook which you want to protect its sheets, and click Kutools Plus > Protect Worksheet > Protect Worksheets.
2. In the Protect Worksheet dialog, you can check the sheets you want to protect as you need from the list. See screenshot:
3. Click Ok, and another dialog pops out for you to entering and retyping the password, see screenshot:
4. Click Ok, and a dialog pops out to remind you successfully protecting worksheets.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite ā Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license ā set up in minutes (MSI-ready)
- Works better together ā streamlined productivity across Office apps
- 30-day full-featured trial ā no registration, no credit card
- Best value ā save vs buying individual add-in