Need vba to loop thru all workbooks in a folder to Protect

DeFacto

New Member
Joined
Jul 29, 2004
Messages
33
Hi!

Looking for vba code that will loop through all workbooks in a folder/directory and password-protect all the workbooks in that folder. The challenge is that the folder/directory will change from month-to-month, so I need vba code that is flexible enough to run through any given folder.

Any assistance you can offer will be greatly appreciated!

Thanks!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

DeFacto

New Member
Joined
Jul 29, 2004
Messages
33
Thanks for the reply!

Actually, the code I've been trying has been a little more basic. After opening all the pertinent workbooks, I've been trying to run the following macro:

Sub ProtectWbks()
Dim Book As Workbook
Password = "password"

For Each Book In Workbooks
On Error Resume Next
Sheets("Plan").Protect Password, True, True, True
Application.ActiveWorkbook.Save
Application.ActiveWorkbook.Close
Next
End Sub

However, I can't get the code to run all the way through... it works on most workbooks but then it hangs up and leaves a handful of workbooks open and unaffected. What am I missing?

Thanks again!
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
Your For loop uses a variable Book to identify each open workbook, one at a time.

However, inside the loop, where do you use that variable? Instead you have an unqualified reference to Sheets() and then you refer to ActiveWorkbook.

What workbook does the unqualified Sheets refer to? Check XL VBA help for the Sheets() property to find out how to make it refer to a particular workbook (Book in your case).

Also, Application.Activeworkbook returns a reference to the active workbook. Instead, you want a reference to Book.
Thanks for the reply!

Actually, the code I've been trying has been a little more basic. After opening all the pertinent workbooks, I've been trying to run the following macro:

Sub ProtectWbks()
Dim Book As Workbook
Password = "password"

For Each Book In Workbooks
On Error Resume Next
Sheets("Plan").Protect Password, True, True, True
Application.ActiveWorkbook.Save
Application.ActiveWorkbook.Close
Next
End Sub

However, I can't get the code to run all the way through... it works on most workbooks but then it hangs up and leaves a handful of workbooks open and unaffected. What am I missing?

Thanks again!
 

Forum statistics

Threads
1,136,655
Messages
5,677,015
Members
419,668
Latest member
DharmaK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top