Automatically Protecting After Input

Excel offers protection for your worksheets, meaning that you can protect the contents of cells so they cannot be changed. Exactly how you use this protection has been discussed in other issues of ExcelTips.

What if you want to allow cells to be edited, but you want them to become protected right after someone enters information in the cell? For instance, you have cells in which a user could enter information, but once entered, you don't want them to have the ability to change the information they entered.

There is no inherent ability in Excel to protect your input after entry, but you can create the ability through the use of a macro. The following macro is an example of how you can do this:

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

    Set MyRange = Intersect(Range("A1:D100"), Target)
    If Not MyRange Is Nothing Then
        Sheets("Sheet1").Unprotect password:="hello"
        MyRange.Locked = True
        Sheets("Sheet1").Protect password:="hello"
    End If
End Sub

This macro assumes that the worksheet has already been protected and that all the cells where you want input to be possible are unlocked. What it does is check to see if the input was done in the proper range of cells, in this case somewhere in the range of A1:D100. If it was, then the worksheet is unprotected, the cell in which information was just entered is locked, and the worksheet is again protected.

If you are using this approach in your own workbook, you will need to modify the potential input range and you will want to change the password used to unprotect and protect the worksheet.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2034) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Automatically Protecting After Input.