Restricting the user to delete the cell contents

Question

Is there's any way to restrict the user from deleting the cell contents without using the protect method of excel. I have this code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
    If Not Intersect(Target, Range("C21:D" & ws.Range("C" & ws.Rows.Count).End(xlUp).Row)) Is Nothing Then
         Cancel = True
         MsgBox "You are not allowed to edit!", vbCritical + vbOKOnly
    EndIf
End sub

But this only disallows the editing of the cell contents. I want make a function that would disallow the editing and deleting the data in a cell without using the protect method. Thanks!

Un-Lock all cells - now select cells you want to protect then Apply Locking then Protect Worksheet. — 0m3r, Mar 07 '16 at 08:01

Luboš Suk · Accepted Answer · 2016-03-07T09:59:01.927

Without lock and unlock, you can use this. We have there one global variable to store selection value (to preserve beforechange state). Function SelectionChange, updating value of current cell, so we can restore cell value after users try.

Sub worksheet_change just controling, if user targeting specified row and column (can be adjusted for whole range), and if he try to change value, he is prompted and value is set back.

Dim prevValue As Variant

Private Sub worksheet_SelectionChange(ByVal target As Range)

    prevValue = target.Value

End Sub

Private Sub worksheet_change(ByVal target As Range)



    If target.Row = 5 And target.Column = 5 Then

        If target.Value <> prevValue Then
            target.Value = prevValue


            MsgBox "You are not allowed to edit!", vbCritical + vbOKOnly

        End If

    End If


End Sub

edit: disable editing every cell which is not empty

Private Sub worksheet_change(ByVal target As Range)



    If prevValue <> "" Then

        If target.Value <> prevValue Then
            target.Value = prevValue


            MsgBox "You are not allowed to edit!", vbCritical + vbOKOnly

        End If

    End If


End Sub

I'm sorry but it is not working. What I want to disable the editing function is the cells where there is a data. But this one still allows the user to edit and delete the content. Thanks! — , Mar 07 '16 at 09:54
It is not so hard to update code as i did now in edited answer. Everything is in condition. There you can specify which cells cant be edited. Curently if cell already has value, you cant change it. If the cell is empty, you can change it. Also you need to store this into sheet object in VBA, not in module — Luboš Suk, Mar 07 '16 at 09:59
and can you describe it more? how you use it and how its not working? — Luboš Suk, Mar 08 '16 at 07:44

score 0 · Answer 2 · answered Jun 14 '20 at 14:22

Try my idea. Copy and paste these codes into the module of the sheet where the protected range is located. In my case it was called "Arkusz1". The protected range is "A1:A10".

Private Sub Worksheet_Change(ByVal Target As Range)
    For Each cell In Range("A1:A10")
        If cell.Text <> Sheets("hidden").Cells(cell.Row, cell.Column) Then
            Call Undoing
        End If
    Next cell
End Sub

Private Sub Undoing()
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
End Sub

In the "This_worksheet" module copy and paste this code:

Private Sub Workbook_Open()
    Sheets.Add
    ActiveSheet.Name = "hidden"
    Sheets("Arkusz1").Range("A1:A10").Copy
    Sheets("hidden").Select
    ActiveSheet.Paste
    Sheets("hidden").Select
    ActiveWindow.SelectedSheets.Visible = False
End Sub

Private Sub Workbook_Close()
    Sheets("hidden").Visible = True
    Sheets("hidden").Delete
End Sub

You will have to change the name of the sheet from "Arkusz1" to "Sheet1" or any other name Your sheet has got. The idea is as follows. Upon the opening of the workbook the application creates a hidden spreadsheet into which it copies the contents of protected cells. For some technical reasons I had to hide the sheet after the copy operation, otherwise it did not work on my computer. Then, any change of the sheet "Arkusz1" triggers the event which compares the contents of the protected range with the same range in the hidden sheet. If there are any differences the application undoes the last action of the user.

Undoing has to be done when event handling by Excel is turned off, because undoing a previous action is also an event of changing the sheet and we would cause a cascade of events - every undo operation would trigger the event "worksheet_change" and it would never stop getting activated.

 Application.EnableEvents = False
 Application.Undo
 Application.EnableEvents = True

Restricting the user to delete the cell contents

2 Answers2

Linked