Skip to content

Automatically Lock Cells After Data Entry

    This tutorial demonstrates how to automatically protect data immediately after it has been entered in a cell.  The steps are:

    Unlock all cells in the worksheet.

    1. To do this select all cells (click in an empty cell and use the shortcut CTRL A to achieve this)
    2. Open the Format Cells dialog (CTRL 1 will achieve this). On the Protection tab untick the Locked property. Click OK.

     

    Create the VBA code to lock cells.

    1. Open the Visual Basic Editor (ALT F11 will achieve this)
    2. In the Project Explorer (if you can’t see the Project Explorer use CTRL R to open it), double-click the sheet that you want to apply this code to.
    3. In the code window, you have two drop-downs. In the first select Worksheet and in the second select Change
    4. Delete the Worksheet_SelectionChange subprocedure as it is no longer needed
    5. In the Worksheet_Change subprocedure add the following code:
    ActiveSheet.Unprotect Password:="YourPassword"
Target.Locked = True
ActiveSheet.Protect Password:="YourPassword"
    1. Use the shortcut ALT F11 to return to your workbook and try it out.