Note: The other languages of the website are Google-translated. Back to English

How to prevent specific cell contents from being deleted in Excel?

This article is talking about preventing specific cell contents from being deleted in Excel. You can achieve it with the methods in this article.

Prevent specific cell contents from being deleted by protecting the worksheet
Prevent specific cell contents from being deleted by using VBA code


Prevent specific cell contents from being deleted by protecting the worksheet

By default, all cells in worksheet are locked. If you want to prevent specific cell contents from being deleted, and make other cells editable after protecting, please do as follows.

1. Click the button on the top left corner of worksheet to select the whole worksheet. Then press Ctrl + 1 keys to open the Format Cells dialog box.

2. In the popping up Format Cells dialog box, uncheck the Locked option under the Protection tab, and then click the OK button. See screenshot:

3. Select the cells you don’t want the contents to be deleted, press Ctrl + 1 keys to open the Format Cells dialog box again, check the Locked option under the Protection tab, and then click the OK button.

4. Now click Review > Protect Sheet, then specify and confirm your password in both the Protect Sheet and Confirm Password dialog boxes.

Now the worksheet is protected. And the specified cell contents won’t be deleted any more.


Prevent specific cell contents from being deleted by using VBA code

Besides the above two methods, you can run VBA code to prevent specific cell contents from being deleted in a worksheet. Please do as follows.

1. Open the worksheet which contains the cell contents you don’t want to be deleted, right click the Sheet Tab, and then click View Code from the right-clicking menu.

2. Copy and paste the below VBA code into the Code window of the Microsoft Visual Basic for Applications window.

VBA code: Prevent specific cell contents from being deleted in Excel

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:E7")) Is Nothing Then Exit Sub
    On Error GoTo ExitPoint
    Application.EnableEvents = False
    If Not IsDate(Target(1)) Then
        Application.Undo
        MsgBox " You can't delete cell contents from this range " _
        , vbCritical, "Kutools for Excel"
    End If
ExitPoint:
    Application.EnableEvents = True
End Sub

Note: In the code, A1:E17 is the range which the cell contents you will prevent from being deleted. You can change the range based on your needs.

From now on, when you trying to delete cell contents from range A1:E17, you will get a Kutools for Excel dialog box as below screenshot shown, please click the OK button.


Related articles:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (17)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Just what I was looking for, thanks for the help!
This comment was minimized by the moderator on the site
don't worry always here to help :)
This comment was minimized by the moderator on the site
Hi there,

I just tried the VBA script and while it works really well in most situations. However it doesn't work when you tab from an editable cell into the non-editable column/cell. The problem arises in that it throws the error message up - as commanded - but it doesn't keep the data that was entered in the original, editable cell which was tabbed from.

Would you have an amendment to the script so that it accepts the data in the editable cell when you tab into un-editable?

Cheers
This comment was minimized by the moderator on the site
Good day,
I tried as you mentioned in your case, but no error throws. Would you provide a screenshot of your case or tell me your Office version?
Thanks for your comment.
This comment was minimized by the moderator on the site
If you don't wan to look your sheet or write VBA code, then I use a simple technique that prevents from any override except for when they use the "DELETE" key (backspace won't be allowed, but Delete is hard to stop). What I did is added a data validation with the following settings:

1) Allow --> List

2) uncheck "Ignore blank" and "in-cell dropdown"

3) In the source enter two double quotes (i.e., "")

4) In Error Alert tab, check "Show error alter after invalid data is entered", select "Stop" for style, then enter an error message and description (e.g., Don't override)
This comment was minimized by the moderator on the site
Hi,
Thanks for sharing.
This comment was minimized by the moderator on the site
VBA Code - Great Tip thanks

Is there a VBA code to Prevent specific cell contents from being modified AND deleted in Excel?
Usual protecting sheet does not work for me as I lose the ability to sort data?

Thanks in advance - Neil
This comment was minimized by the moderator on the site
"Prevent specific cell contents from being deleted by protecting the worksheet"I did as per your guideline, still cell information could be deleted i.e. protection does not work. Any solution.
This comment was minimized by the moderator on the site
Hi Ahsan,
Did you apply the VBA method?
You need to add the VBA to the worksheet code window. Supposing the cell contents you want to protect are in Sheet9, please right click the sheet tab and select View Code from the context menu, and then directly copy the code into the code window as the below image shown.
This comment was minimized by the moderator on the site
Hello, this code works well, however deleting row/collumn will bypass it. Is there any workaround to this? Cheers, David.
This comment was minimized by the moderator on the site
Hi David,
Which Excel version are you using? I have tried the code, entire rows and columns in the specified range can't be deleted after applying the code.
This comment was minimized by the moderator on the site
Thank you for the valuable information.
Regarding the below VBA:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:E7")) Is Nothing Then Exit Sub
On Error GoTo ExitPoint
Application.EnableEvents = False
If Not IsDate(Target(1)) Then
Application.Undo
MsgBox " You can't delete cell contents from this range " _
, vbCritical, "Kutools for Excel"
End If
ExitPoint:
Application.EnableEvents = True
End Sub

The above will allow execution within a certain specified range. It does not allow the addition of new rows. Is there a way that new rows can be added yet maintaining the same protection.
thanks and regards
This comment was minimized by the moderator on the site
Hi Oussama Abou faraj,
After trying with various methods, I can't seem to get this to work. After adding a new row, the area will be locked Immediately and the newly inserted cell cannot be edited.
This comment was minimized by the moderator on the site
Is it possible to do something similar at workbook level?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:M7000")) Is Nothing Then Exit Sub
On Error GoTo ExitPoint
Application.EnableEvents = False
If Not IsDate(Target(1)) Then
Application.Undo
MsgBox " You can't delete cell contents from this range " _
, vbCritical, "Kutools for Excel"
End If
ExitPoint:
Application.EnableEvents = True
End Sub
This comment was minimized by the moderator on the site
Hi Gelu,
Do you mean to prevent the range "A1:M7000" from being deleted in all worksheets of the current workbook?
This comment was minimized by the moderator on the site
No, I meant NOT to allow deletion (without protecting the wks or wkb).

It is nice the above works at Wks level.

If there is something to make it work at wkb level it would be even better (I have many sheets)

G
This comment was minimized by the moderator on the site
This is great. I was wondering if this was an option and sure enough it was. Thank you for the very easy instructions.

Andres S.
Rated 5 out of 5
There are no comments posted here yet

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL