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
Bookmarks