Macro for protecting all the worksheets with password

[DLimKTS]

Hello,

I have about over 200 xls files and was asked to protect all the worksheets with password from being able to edit and print. Is there a macro available to protect them by batch? In need of help. Thank You.

 

[Momentman]

Do you mean 1 workbook with 200 worksheets or 200 workbooks?

The password could prevent users from editing but i don't think that would prevent them from printing except one uses say a Beforeprint event"

 

[mumps]

Try this macro. Replace "mypassword" with a password of your choosing.

Sub ProtectSheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets
        ws.Protect Password:="mypassword"
    Next ws
    Application.ScreenUpdating = True
End Sub

This will protect all the sheets in the active workbook.

 

[rollis13]

Are the files all in the same folder ? Since you need to avoid printing that probably would need the use of a macro, in what format are these files saved ? in other word, which version of Excel are you using ?

 

[DLimKTS]

Thanks MumpsI have over 200 workbooks in the same folder and the worksheet in each workbook varies.

My goal is to protect all the worksheets in all the workbooks by using one batch file Macro. Are both xls and xlsx files.

 

[mumps]

Open Excel and copy/paste this macro into a regular module and run it from there. Make sure that all your files are in one folder and change the "Mypath" line in the code to match your actual path. Also change "mypassword" to one of your choosing. Please keep in mind that all the files in that folder will have their sheets protected so if you have some files whose sheets you don't want protected, move them to another folder.

Sub CopyRanges()
     Dim wkbSource As Workbook
     Dim MyPath As String
     Dim MyFile As String
     Dim ws As Worksheet
     Application.ScreenUpdating = False
     MyPath = "C:\Test" 'modify to match the path to your folder
     If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
     MyFile = Dir(MyPath & "*.xls*")
     Do While Len(MyFile) > 0
         Set wkbSource = Workbooks.Open(MyPath & MyFile)
         With wkbSource
             For Each ws In Sheets
                 ws.Protect Password:="mypassword"
             Next ws
             .Close savechanges:=True
         End With
         MyFile = Dir
     Loop
     Application.ScreenUpdating = True
 End Sub

 

[DLimKTS]

Mumps,

I ran the macro but I got a error "Run-time error' '13'; Type mismatch" and when I go to debug, this line "
For Each ws In Sheets" is highlighted.

 

[mumps]

When I tried it with some dummy files, it worked properly. Did you update the file path in the code to match yours?

 

[DLimKTS]

May I see your exact syntax or what you've put in the macro?

 

[mumps]

I created a folder named "Test" in the root of Drive C and then copied four sample Excel files into that folder. I included files with xls and xlsx extensions to make sure the macro worked on both types of files. I used the macro as I posted it in Post #6. If you are still having problems, try creating a folder named "Test" on your C Drive and then place all your files in that folder. This way you don't have to modify the code in any way. If it worked for me, it should work for you.