VBA to set password based on cell value

Thanks TMShucks with your quick response. Column AQ will be a hidden column and the value in AQ11 will be the password I'd want to use, such as bt2 or hr2 or finance7...etc. This file will also be locked down from users making most changes...a few columns will be available for them to edit but for the most part they will not be able to modify it. I have that part all set up and ready to go, I'm just trying now to set passwords to open...do you have suggestions on how to qualify the range? Thanks again!

Thanks again TM...! The sheet won't be hidden, just a column. Maybe I need a suggestion. Here is basically my task:
1) I'll have one folder that will contain about 50 workbooks.
2) I want to use the code I posted earlier to open these 50 workbooks and saveas with a unique password.
3) Perhaps some kind of CASE Statement that might read cell AQ11 (which contains a Business Unit name) and then assign the unique password..? Suggestions?

Something like:

Please Login or Register  to view this content.

Then in the save as portion it might have the DIM of the case statement...

Please Login or Register  to view this content.

I just am not sure exactly how the code would work...thoughts on this idea? Does it make sense? Again, I do appreciate your efforts on this...this excel forumn is a great source!

TM - - I started playing around with CASE code and the code below is not quiet right. I've saved the VBA code in one workbook saved as VBApwCode.xls. What I did find out in my testing was that if I added a value in workbook VBApwCode.xls in cell AQ11 the text: Marketing, then this part of the code works. But I need it to read the value in the workbooks I'm wanting to open then saveas then close...make sense? How do I can the code below to read the workbooks it's opening then saveas then closing?

Please Login or Register  to view this content.

Hi TM (and all you other vba-gurus). I've taken the beginning code above and modified it to what I want...almost.

To recap - - I have a folder that contains 50 workbooks. I want the VBA code to open each of these workbooks, check the value of cell AQ11 and using a Select Case statement assign a password based off that cell value. The code below works, but what I'm wondering if any of you might have thoughts on how to stream line the code. I have some VBA skills but nothing that matches the stuff you all can produce! Anyway, I'm sure there many ways to improve this code...so all suggestions welcome! The below code opens and saves 3 workbooks. Again, there are about 50 so I would like something that completes the task in a more stream line fashion.

Please Login or Register  to view this content.

Thanks TMShucks - I'm getting really close but I'm now stuck on the section that starts the CASE select. I am getting a "run time error - 424 Object Required" when it hits this line. The first file in the folder does open and the file name does get populated in cell A1 ( I left that part in to see what gets placed there).

Please Login or Register  to view this content.