How to Safeguard Your Excel Data Entries

In Excel, ensuring the security of your data entries is essential, but what if you want to allow users to add information while preventing them from deleting previous data? This step-by-step guide will show you how to achieve this in your Excel workbook.

Step 1: Open Your Excel Workbook

Begin by opening your Excel workbook that contains the data you want to protect.

Step 2: Select the Relevant Sheet or Range

Choose the worksheet or specific range within which you wish to enable data entry and protect existing data.

Step 3: Access Cell Formatting Options

Open the “Format Cells” dialog by right-clicking on your selected range or using the keyboard shortcut CTRL + 1.

Step 4: Clear the Locked Option

Within the “Format Cells” dialog, go to the “Protection” tab and make sure the “Locked” option is cleared. This will allow you to protect the sheet effectively.

Step 5: Enable Sheet Protection

For users of Excel 2019 and later versions, navigate to the “Review” tab and click on “Protect Sheet.”

Step 6: Set a Password

Enter a password of your choice and click “OK.” You will be asked to confirm the password by entering it again, after which, click “OK” once more.

Step 7: Access Visual Basic for Applications (VBA)

Right-click on the name of the worksheet (e.g., “Sheet1”) and select “View Code.” This will open the Visual Basic for Applications (VBA) editor.

Step 8: Add Custom Code

In the VBA editor, enter the following code, adjusting the range and using the same password you set in Step 6:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ips As Range
    On Error Resume Next
    Set ips = Intersect(Range("A1:AM2000"), Target)
    If ips Is Nothing Then Exit Sub
    Target.Worksheet.Unprotect Password:="YourPassword"
    ips.Locked = True
    Target.Worksheet.Protect Password:="YourPassword"
End Sub

Replace “YourPassword” with the actual password you set in Step 6.

Step 9: Save and Test

Close the VBA code editor, save your workbook, and test it. Users can now enter data in the specified range but won’t be able to delete existing data. Your Excel data is now safeguarded effectively.

By following these steps, you can enhance the security of your Excel data while allowing for seamless data entry.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More post