How to automatically protect a sheet after data entry using VBA?

To automatically protect a worksheet after data entry using VBA, you can use the `Worksheet_Change` event in the VBA editor. This event triggers every time a change is made to the worksheet, allowing you to apply protection immediately after data is entered. Here’s how you can implement this:

   Private Sub Worksheet_Change(ByVal Target As Range)
       Dim ws As Worksheet
       Set ws = Me ' Refers to the sheet where the code is implemented

       ' Unprotect sheet to allow changes
       ws.Unprotect Password:="yourPassword"

       ' Check if the change is a single cell or range of cells
       If Not Intersect(Target, ws.UsedRange) Is Nothing Then
           ' Perform any additional logic here if required
            
           ' Protect the sheet immediately after a change
           ws.Protect Password:="yourPassword"
       End If
   End Sub

Open the VBA editor:

Press `ALT + F11` in Excel to open the Visual Basic for Applications editor.

Access the specific worksheet code:

In the VBA editor, locate the “Project Explorer” (if it’s not visible, press `CTRL + R`).
Find the workbook in which you want to protect a sheet after data entry.
Double-click on the sheet name (e.g., `Sheet1`) where you want to apply this feature. A code window should open.

Insert the VBA code:

In the code window, paste the following VBA code:

Customize the code:

Replace `”yourPassword”` with a desired password. This password will be used to protect your sheet.
You can add any additional logic within the `If Not Intersect(Target, ws.UsedRange) Is Nothing Then` block if needed (e.g., skipping protection for certain ranges).

Close the VBA editor:

Close the VBA editor and return to Excel by pressing `ALT + Q`.

Test your setup:

Try entering data into the sheet. After you finish typing or modifying any cell and press Enter, the sheet should become automatically protected.

This code temporarily unprotects the sheet to allow the change to be registered and then protects the sheet immediately after a change. Note that password protection in Excel is not entirely secure, and the password can be cracked with some specialized tools, but it should suffice for basic security purposes.

facebooklinkedin