Skip i'r prif gynnwys

Sut i atal cynnwys celloedd penodol rhag cael ei ddileu yn Excel?

Mae'r erthygl hon yn sôn am atal cynnwys celloedd penodol rhag cael ei ddileu yn Excel. Gallwch ei gyflawni gyda'r dulliau yn yr erthygl hon.

Atal cynnwys celloedd penodol rhag cael ei ddileu trwy ddiogelu'r daflen waith
Atal cynnwys celloedd penodol rhag cael ei ddileu trwy ddefnyddio cod VBA


Atal cynnwys celloedd penodol rhag cael ei ddileu trwy ddiogelu'r daflen waith

Yn ddiofyn, mae'r holl gelloedd yn y daflen waith wedi'u cloi. Os ydych chi am atal cynnwys celloedd penodol rhag cael ei ddileu, a gwneud celloedd eraill yn olygadwy ar ôl eu gwarchod, gwnewch fel a ganlyn.

1. Cliciwch ar y botwm ar gornel chwith uchaf y daflen waith i ddewis y daflen waith gyfan. Yna pwyswch Ctrl + 1 allweddi i agor y Celloedd Fformat blwch deialog.

2. Yn y popping up Celloedd Fformat blwch deialog, dad-diciwch y Dan glo opsiwn o dan y Diogelu tab, ac yna cliciwch ar y OK botwm. Gweler y screenshot:

3. Dewiswch y celloedd nad ydych chi am i'r cynnwys gael eu dileu, pwyswch Ctrl + 1 allwedd i agor y Celloedd Fformat blwch deialog eto, gwiriwch y Dan glo opsiwn o dan y Diogelu tab, ac yna cliciwch ar y OK botwm.

4. Nawr cliciwch adolygiad > Diogelu Dalen, yna nodwch a chadarnhewch eich cyfrinair yn y ddau Diogelu Dalen ac cadarnhau Cyfrinair blychau deialog.

Nawr mae'r daflen waith wedi'i gwarchod. Ac ni fydd cynnwys penodol y gell yn cael ei ddileu mwyach.


Atal cynnwys celloedd penodol rhag cael ei ddileu trwy ddefnyddio cod VBA

Heblaw am y ddau ddull uchod, gallwch redeg cod VBA i atal cynnwys celloedd penodol rhag cael ei ddileu mewn taflen waith. Gwnewch fel a ganlyn.

1. Agorwch y daflen waith sy'n cynnwys cynnwys y gell nad ydych chi am gael ei dileu, cliciwch ar y dde ar y Tab Dalen, ac yna cliciwch Gweld y Cod o'r ddewislen clicio ar y dde.

2. Copïwch a gludwch y cod VBA isod i mewn i ffenestr Cod y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.

Cod VBA: Atal cynnwys celloedd penodol rhag cael ei ddileu yn 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

Nodyn: Yn y cod, A1: E17 yw'r amrediad y bydd cynnwys y gell y byddwch yn ei atal rhag cael ei ddileu. Gallwch newid yr ystod yn seiliedig ar eich anghenion.

O hyn ymlaen, pan fyddwch chi'n ceisio dileu cynnwys celloedd o ystod A1: E17, fe gewch chi a Kutools ar gyfer Excel blwch deialog fel isod dangosir y screenshot, cliciwch y OK botwm.


Erthyglau perthnasol:

Offer Cynhyrchiant Swyddfa Gorau

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog    VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr    Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd    Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

Disgrifiad


Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Comments (18)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Hi,

This is exactly what I was looking for, but it doesn't work for me...
This says: "End Sub expected"

Can you help me ?

Thanks :)
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
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
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
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
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
"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
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
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
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.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations