How can I automatically protect only certain cells in Excel 2010

Logit · #9 03-26-2017, 08:00 PM

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How Do You Drag & Drop Cells in Excel 2010?	juliansdad	Excel	4	12-05-2013 01:15 AM
Linking cells on Excel 2010	teza2k06	Excel	1	02-05-2013 01:03 AM
Excel VBA: Worksheet_Change Cells.Protect	tinfanide	Excel Programming	8	04-08-2012 09:58 PM
Protect / Lock Cells / Print Set-up?	meggenm	Excel	3	01-26-2012 09:57 PM
Automatically entering/fill data in cells in Excel 2003	dipdog	Excel	0	08-17-2006 08:37 AM