SOLVED

Automatically protect cells in a file once the file is saved and closed by a user

Copper Contributor

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 sharepoint link with 10 users (inside and outside the organization). The spreadsheet has the majority of cells protected except for a selection of cells that are not and where the users can enter their records.

 

Objective: I want to have the record entered by a users to be locked once the file is saved and closed by this user. I want the following user not to be able to modify what the initial user entered.

 

Attempt: I have attempted the following code but it doesn't meet the need as the cell is automatically locked once a value is entered. I want to give the user the possibility to modify the cells until they save a close the file.

Moulahoum1991_0-1720443443584.png

 

I appreciate any orientation to solve this.

 

Cheers

 

4 Replies

@Moulahoum1991 

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 Sub

@HansVogelaar 

Thank 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 Sub

 However 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).

 

Moulahoum1991_0-1720540032337.png

 

 

I'd appreciate a way forward with this.

 

Thanks a ton !

best response confirmed by Moulahoum1991 (Copper Contributor)
Solution

@Moulahoum1991 

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

@HansVogelaar 

 

This works perfectly. Thank you so much for your help.

1 best response

Accepted Solutions
best response confirmed by Moulahoum1991 (Copper Contributor)
Solution

@Moulahoum1991 

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

View solution in original post