asked on

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?

ASKER CERTIFIED SOLUTION

paul_dj

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bbarrene

ASKER

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

paul_dj

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!