VBA - when click save button lock all cells with data in the workbook.

Hello all,

I know its not something new and I have gone through many threads where the cell gets locked if its contains data. That is when we type and move to another cell it locks the cell which contain data.
However, what I am looking for is, the cell should get locked only if:
1. The cell is not blank and user click save (Ctrl+S)
2. While closing we get an option to save and if the user clicks "OK".

Because the user should get chance to amend any cell while reviewing and identifies typo error or had selected any incorrect option from a combo box (data validation).

I have gone through below code and tried few stuffs but no idea how to involve save function in it which will lock all the cells in the workbook with data.
Can anyone help me out with this.

Dim blnUnlockedAllCells As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const RangeToLock As String = "A2:D1000" '<<  adjust to suit
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not blnUnlockedAllCells Then
        Me.Cells.Locked = False
        On Error Resume Next
        Me.Range(CStr(RangeToLock)).SpecialCells(2).Locked = True
        On Error GoTo 0
        blnUnlockedAllCells = True
        Me.Protect Password:="pwd", userinterfaceonly:=True
    End If
    
    If Not Application.Intersect(Target, Me.Range(CStr(RangeToLock))) Is Nothing Then
        If Len(Target) Then Target.Locked = True
    End If
    
End Sub

Checkout the Workbook_beforesave event, it is located in the workbook module.
.
WB_BeforeSave.jpg

hello Davesexcel, thanks a lot for your response however, I tried it but getting an error.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim blnUnlockedAllCells As Boolean
    
    Const RangeToLock As String = "A2:D1000" '<<  adjust to suit
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not blnUnlockedAllCells Then
        Me.Cells.Locked = False
        On Error Resume Next
        Me.Range(CStr(RangeToLock)).SpecialCells(2).Locked = True
        On Error GoTo 0
        blnUnlockedAllCells = True
        Me.Protect Password:="pwd", userinterfaceonly:=True
    End If
    
    If Not Application.Intersect(Target, Me.Range(CStr(RangeToLock))) Is Nothing Then
        If Len(Target) Then Target.Locked = True
    End If
End Sub

and event I tired a new code as well but its not going with the BeforeSave event could you please help?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet: Set ws = ActiveSheet

ActiveSheet.Unprotect Password:="test"

    If Not Intersect(Target, Range("F:F", "G:G")) Is Nothing Then
        If Target.Value <> vbNullString Then Target.Locked = True
    End If
ActiveSheet.Protect Password:="test"

End Sub