VBA - make backup of workbook and protect cells

[robertvdb]

Dear all,

I have a workbook which I want to distribute among other users, but I do not want that they apply any changes to the content. So basically, it is for "read only".
I guess I have to password-protect the backup copy, and lock the cells there.
How do I write this in VBA ?

Thanks

 

[Saurabhj]

Hi,

Use below code to protect your workbook structure and all sheets in backup copy.

Sub createBackup()
Dim WB As Workbook
Dim filePath As String

'New file name contains todays date and will save in the same folder.
filePath = ThisWorkbook.Path & "\" & _
    Format(Now(), "DDMMYYHHMMSS") & "_" & _
        Left(ThisWorkbook.Name, InStr(1, ThisWorkbook.Name, ".") - 1) & ".xlsm"

'Creating backup file
ThisWorkbook.SaveCopyAs Filename:=filePath

Set WB = Workbooks.Open(filePath)
'To protect workbook structure
WB.Protect Password:="hello"


'to Protect all worksheets
For sheetnum = 1 To WB.Sheets.Count
    WB.Sheets(sheetnum).Protect Password:="pwd"
Next
End Sub

 

[robertvdb]

Thanks Saurabhj. Most helpful !

 

[Saurabhj]

My pleasure. Thanks for the feedback.