How can I automatically protect only certain cells in Excel 2010

KelStin · #1 03-26-2017, 01:29 PM

I have a multiple user spreadsheet which is used to pass information from shift to shift. I have a drop down used to choose which person entered information and I am using a NOW function to automatically enter date and time once something is selected in the drop down. I would like to make the date and time lock in upon entry, or even the entire row would be fine. I only want only the row used to be protected while the remaining blank rows can still be used. Any help is greatly appreciated.

Logit · #2 03-26-2017, 03:36 PM

Paste this into the Sheet Level module for the worksheet.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRange As Range

    Set MyRange = Intersect(Range("A1:D100"), Target)  '<-- change range of rows here
    If Not MyRange Is Nothing Then
        Sheets("Sheet1").Unprotect password:="hello"  '<-- this is your password, change as required.
        MyRange.Locked = True
        Sheets("Sheet1").Protect password:="hello"
    End If
End Sub

KelStin · #3 03-26-2017, 03:53 PM

Thank you for the reply. Unfortunately, this doesn't do what I need. I want to only lock, for instance, A1 and A2 when something is entered into row A. When something is entered into row B, lock B1 and B2, and so on. This can be upon entry or upon save, but I need to have the remaining blank rows to be usable whenever the sheet is opened. Again, thank you for the response, I haven't been able to figure out how to get what I want it to do.

Logit · #4 03-26-2017, 04:30 PM

Paste this macro into the Sheet Level module:

Code:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Value <> "" Then
        changeInput = MsgBox("Do you want to unlock the sheet?", vbYesNo + vbQuestion, "Unlock sheet")
        If changeInput = vbYes Then
            Dim pass As String
            pass = InputBox("Enter Password")
                If pass <> "password" Then
                    MsgBox ("Wrong password")
                Else
                    ActiveSheet.Unprotect Password:="password"
                    Target.Locked = False
                End If
        End If
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim cel As Range
    ActiveSheet.Unprotect Password:="password"
    For Each cel In Target
        If cel.Value <> "" Then
            cel.Locked = True
        End If
    Next cel
    ActiveSheet.Protect Password:="password"
End Sub

Logit · #5 03-26-2017, 04:42 PM

This version allows you to double-click the cell, enter the password, then change the data there in. Keeps you from having to constantly go to the Menu Bar to enter a password.

Code:

Option Explicit
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 = "password"
    If PassWord = "password" Then
    Dim cel As Range
    ActiveSheet.Unprotect PassWord:="password"
    For Each cel In Target
        If cel.Value <> "" Then
            cel.Locked = True
        End If
    Next cel
    End If
  
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim cel As Range
    ActiveSheet.Unprotect PassWord:="password"
    For Each cel In Target
        If cel.Value <> "" Then
            cel.Locked = True
        End If
    Next cel
    ActiveSheet.Protect PassWord:="password"
End Sub

KelStin · #6 03-26-2017, 05:12 PM

I like the password box pop up, that is a lot easier than going to the toolbar. However, this still locks the entire sheet after any entry. I need only a couple of cells to lock, or if it could be made to only lock upon save, entire rows could lock once they contain data in all required cells.

Logit · #7 03-26-2017, 05:38 PM

Do you know which cell / s the data will be entered ?

If so, the code might be edited to refer to a range (the cell /s affected) rather than the active sheet.

KelStin · #8 03-26-2017, 05:59 PM

there will be running entries in 10 or so columns with each entry using only one row. I need to lock column A and B only in a row that information has been entered. the sheet will continue to populate for several rows, maybe once a month it will be saved and restarted. There could end up being a couple of hundred rows used. I have a "NOW" function to record when the info is entered in each row, but the date and time currently updates, in all rows, any time info is added to any row of sheet, this is what I want to avoid. I want to hold the date and time in each row without locking the unused portion of the sheet.

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