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.
