CODEWITHSUNDEEP
excelvba
Edward144
Edward144

Reputation: 487

Make workbook read only

I have a workbook that is automatically updated by a VBA macro.

To get around the potential issue of a user having the workbook open in read-write when the macro tries to update it I am setting the attribute to read-only when the macro saves it, and then turning it off when it opens the book for editing.

SetAttr ThisWorkbook.Path & "\booktoupdate.xlsx", vbNormal

'Paste in some values

SetAttr ThisWorkbook.Path & "\booktoupdate.xlsx", vbReadOnly

However any user could go into the properties of the file and turn off read only.

Is there any way to protect the file so read-only can only be changed by VBA?

Upvotes: 2

Views: 10211

Answers (1)

Dvyn Resh
Dvyn Resh

Reputation: 980

This will exactly suit your requirement: while saving workbook you can enable with password like below,

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\booktoupdate.xlsx", _
    WriteResPassword:="enter your password", ReadOnlyRecommended:=True

when other user try to edit it will prompt for password and excel remains in read only mode

Upvotes: 3

Related Questions