Forum Discussion

Moulahoum1991's avatar
Moulahoum1991
Copper Contributor
Jul 08, 2024
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 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.

 

I appreciate any orientation to solve this.

 

Cheers

 

excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jul 09, 2024

    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

4 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 08, 2024

    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
    • Moulahoum1991's avatar
      Moulahoum1991
      Copper Contributor
      Jul 09, 2024

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

       

       

       

      I'd appreciate a way forward with this.

       

      Thanks a ton !

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jul 09, 2024

        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

Resources