Forum Discussion
Solved
Automatically protect cells in a file once the file is saved and closed by a user
Hello fellow Excel Users, I am not to automating tasks in Excel and would like your support with the following : Description: I have am excel spreadsheet and I am sharing through a sharepoin...
Is this better?
Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next With Worksheets("My Sheet") .Unprotect Password:="OurPassword" .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Cells.SpecialCells(xlCellTypeConstants).Locked = True .Protect Password:="OurPassword" End With End Sub
Instead of creating code in the worksheet module, create code in the ThisWorkbook module:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Worksheets("Protect After Save")
.Unprotect Password:="Our Password"
.Cells.Locked = True
.Protect Password:="Our Password"
End With
End SubThank you very much for this orientation. It is getting there.
I applied the VBA code on ThisWorkBook before closure following this code:
Private Sub Workbook_BeforeClose(Cancel As Boolean) With Worksheets("My Sheet") .Unprotect Password:="OurPassword" .Cells.Locked = True .Protect Password:="OurPassword" End With End SubHowever these are the observations:
1- I am able to enter the record in the cells there were kept unprotected. However, when I close the file, all cells become protected which is not what I want.
2- I just the entered cells to be protected, and those that were not to be kept unprotected (locked unchecked).
I'd appreciate a way forward with this.
Thanks a ton !
Is this better?
Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next With Worksheets("My Sheet") .Unprotect Password:="OurPassword" .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Cells.SpecialCells(xlCellTypeConstants).Locked = True .Protect Password:="OurPassword" End With End Sub
