Click in the upper left corner of the sheet, selecting all cells on the worksheet.
1. Please unlock this range first, choose Format Cells in the right-clicking menu, and in the Format Cells dialog box, unchecking the Locked box under the protection tab, and finally clicking the OK button.
2. Click Review > Protect Sheet. And specify a password to protect this worksheet. (In the attached example the code is
abc )
3. Right click the sheet tab, select View Code from the right-clicking menu. Then copy and paste the below VBA code into the Code window.
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
On Error Resume Next
Set xRg = Intersect(Target, Range("C10, D10, D15")) '<--- change the cell references as required.
If xRg Is Nothing Then Exit Sub
Target.Worksheet.Unprotect PassWord:="abc"
xRg.Locked = True
Target.Worksheet.Protect PassWord:="abc"
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim PassWord As String, i As Integer
i = 0
Do
i = i + 1
If i > 3 Then
MsgBox "Sorry, Only three tries"
Application.DisplayAlerts = False
ThisWorkbook.Saved = True
Application.Visible = False
Application.Quit
Exit Sub
End If
PassWord = InputBox("Enter Password")
Loop Until PassWord = "abc"
If PassWord = "abc" Then
Dim cel As Range
ActiveSheet.Unprotect PassWord:="abc"
For Each cel In Target
If cel.Value <> "" Then
cel.Locked = True
End If
Next cel
End If
End Sub
Note: In the code:Set xRg = Intersect(Target, Range("C10, D10, D15")) '<--- change the cell references as required; and “abc” is the password of this protected worksheet. Please change them as you need.
I'm hopeful you can add as many cell references as needed without creating an error. I haven't tried more than what is already in the sample project. You might want to test with 10 - 20 cells at first and run the project. If that works, try an additional 10 - 20 more cells and test again. No sense in adding 300 cell references only to discover it will only work with 30 cells.
Save the workbook as ".xlsm" (macro enabled).
After finishing entering data to the cells of Range("C10, D10, D15"), they will be locked automatically. And you will get a prompt dialog box if you try to change any cell content of this range.