Hello Guyz,
I need a help from those who are good at macro codes. Recently i decided to protect the excel files in my office. But its a tedious task to individually add password protection to the 1000's of excel files located in different folders and sub folders. There i started to search for suitable macro codes. And my search ended in getting a code similar to what i need. But i need to modify this code to suit it for my purpose.
What I require :-
1. The code should encrypt the files. The current code i recieved will only protect the files from editing. For me, files should not be viewed by anyone else with out entering the password. I want to encrypt the files
2. I want the macro code to search for excel files in folders and Subfolders. The current code only protect those files in the folder i selected.
So can anyone modify the following code to meet my purpose. It will be a great for me.
The code given below will :-
Prompt you to select a folder
Prompt you to select PROTECT or UNPROTECT action on the files in that folder
Prompt you to enter a password to use
Process all the files in that folder with options above
Give a count afterward of how many files were affected
Option Explicit
Sub SetProtectionInAllSheetsAllFilesInFolder()
'JBeaucaire 3/4/2010
'Select a folder and provide password to protect/unprotect all Excel files in folder
Dim fPath As String, fName As String, OldDir As String
Dim pwd As String, pwd2 As String, ws As Worksheet
Dim Ans As Long, Cnt As Long
'Folder selection
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then fPath = .SelectedItems(1) & "\"
End With
'Choose whether to protect or unprotect the files
Ans = Application.InputBox("Are we protecting or unprotecting the files in this folder?" & vbLf & vbLf & _
"Enter 1 - protect files" & vbLf & "Enter 2 - unprotect files" & vbLf & vbLf & _
"Any other value or CANCEL will abort", "Protect or Unprotect?", Type:=1)
If Ans < 1 Or Ans > 2 Then Exit Sub
'Password w/verification
Do
pwd = Application.InputBox("What password to use?", "Enter Password", Type:=2)
If pwd = "False" Then Exit Sub
pwd2 = Application.InputBox("Please enter the password again for verification?", "Re-Enter Password", Type:=2)
If pwd2 = "False" Then Exit Sub
If pwd = pwd2 Then Exit Do Else MsgBox "Passwords did not match, please try again"
Loop
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
OldDir = CurDir
ChDir fPath
fName = Dir("*.xls")
'File protection
Do While Len(fName) > 0
Workbooks.Open fName
For Each ws In ActiveWorkbook.Worksheets
If Ans = 1 Then ws.Protect Password:=pwd Else ws.Unprotect Password:=pwd
Next ws
ActiveWorkbook.Close True
fName = Dir
Cnt = Cnt + 1
Loop
ChDir OldDir
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox "A total of " & Cnt & " files were processed."
End Sub
Bookmarks