Macro to Make Excel Workbook Read Only

I have a workbook (1st wkb) that transfers data to another workbook (2nd wkb) and also runs a macro from the 2nd wkb to transfer data to a last Workbook (3rd wb) all on the push of a button. The reason for doing this is because the 2nd wkb will serve as a library card. As the information is changed in the library card, the information in the 3rd workbook will update as well. On the push of a button, the first wkb opens the 2nd wkb which is initially an Excel template file and then saves it as a new file. I would like to make this file read only when I save it as the new file name and assign it a password so that only the person who knows the password can make changes. I know there is a way to make the file read only when it is first opened by applying the code to the "Workbook_Open" code but I dont believe I could use that since I am starting off with a template file and need to be able to make changes to the new saved file when I enter the correct password.  Is there anyway I could this through VBA?
bbarreneAsked:
Who is Participating?
 
paul_djCommented:
Adding something like shown below when saving the new workbook should do the trick:

ActiveWorkbook.SaveAs Filename:="C:\Users\bbarrene\Desktop\your_workbook.xlsm", _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, WriteResPassword:="your_pass", _
    ReadOnlyRecommended:=True

Change the path and password as necessary.
0
 
bbarreneAuthor Commented:
Thanks for the help but the FileFormat did not work for me. I needed to modify the code to:
ActiveWorkbook.SaveAs Filename:="C:\Users\bbarrene\Desktop\your_workbook.xlsm", _
    FileFormat:=xlWorkbookNormal, WriteResPassword:="your_pass", _
    ReadOnlyRecommended:=True

Open in new window

0
 
paul_djCommented:
Great, glad you sorted it out.  I wasn't sure what file type you were going to Save As, so I just left it as the type I had in there at the time (XLSM).

Thanks for accepting my solution.  :)

Cheers!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.