跳到主要內容

如何防止在Excel中刪除特定單元格內容？

本文討論的是防止在Excel中刪除特定的單元格內容。 您可以使用本文中的方法來實現。

通過保護工作表防止特定的單元格內容被刪除
防止使用VBA代碼刪除特定的單元格內容

通過保護工作表防止特定的單元格內容被刪除

默認情況下，工作表中的所有單元格均被鎖定。 如果要防止刪除特定單元格內容，並在保護後使其他單元格可編輯，請執行以下操作。

1。 點擊 按鈕在工作表的左上角以選擇整個工作表。 然後按 按Ctrl + 1 鍵打開 單元格格式 對話框。

2.在彈出 單元格格式 對話框中，取消選中 鎖定 選項下 保護 選項卡，然後單擊 OK 按鈕。 看截圖：

3.選擇您不想刪除內容的單元格，按 按Ctrl +1鍵打開 單元格格式 再次出現對話框，請檢查 鎖定 選項下 保護 選項卡，然後單擊 OK 按鈕。

4.現在點擊 回顧 > 保護工作表，然後在兩個 保護工作表 及 確認密碼 對話框。

現在，工作表已受保護。 並且指定的單元格內容將不再被刪除。

防止使用VBA代碼刪除特定的單元格內容

除了上述兩種方法，您還可以運行VBA代碼以防止特定的單元格內容在工作表中被刪除。 請執行以下操作。

1.打開包含您不想刪除的單元格內容的工作表，右鍵單擊“工作表”選項卡，然後單擊“確定”。 查看代碼 從右鍵單擊菜單中。

2.複製下面的VBA代碼並將其粘貼到 Microsoft Visual Basic for Applications 窗口。

VBA代碼：防止在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

備註：在代碼中，A1：E17是您將防止刪除的單元格內容的範圍。 您可以根據需要更改範圍。

從現在開始，當您嘗試從範圍A1：E17刪除單元格內容時，您將獲得一個 Kutools for Excel 對話框如下圖所示，請點擊 OK 按鈕。

相關文章:

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

增強您的 Excel 技能 Kutools for Excel，體驗前所未有的效率。 Kutools for Excel 提供 300 多種高級功能，可提高工作效率並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905

Office Tab 為 Office 帶來選項卡式界面，讓您的工作更輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (17)
Rated 5 out of 5 · 1 ratings
Andres S
Andres S
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.
Reply
0
0
Rated 5 out of 5
Gelu
Gelu
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
Reply
Report
0
0
Gelu
Gelu
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
Reply
0
0
crystal
crystal     Gelu
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?
Reply
Report
0
0
Oussama Abou faraj
Oussama Abou faraj
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
Reply
0
0
crystal
crystal     Oussama Abou faraj
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.
Reply
Report
0
0
David
David
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.
Reply
0
0
crystal
crystal     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.
Reply
Report
0
0
Ahsan
Ahsan
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.
Reply
0
0
crystal
crystal     Ahsan
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.
View Attachments (1 / 5)
Reply
Report
0
0
neil
neil
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
Reply
Report
0
0
George M.
George M.
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)
Reply
0
0
crystal
crystal     George M.
This comment was minimized by the moderator on the site
Hi,
Thanks for sharing.
Reply
Report
0
0
Si
Si
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
Reply
0
0
crystal
crystal     Si
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.
Reply
Report
0
0
HJR
HJR
This comment was minimized by the moderator on the site
Just what I was looking for, thanks for the help!
Reply
0
0
finn
finn     HJR
This comment was minimized by the moderator on the site
don't worry always here to help :)
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations