Undo delete on locked cells (VBA)

[Craig__]

I have a worksheet I want to keep unprotected but still prevent data in locked cells from being deleted. I tried using a Worksheet_Change event but can’t get it to work. Your help would be much appreciated.

When a range of cells is selected (cells could be a mixture of locked and unlocked), and the Delete key is pressed, I want the macro to delete only the data in unlocked cells.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Selection, Columns("A:G")) Is Nothing Then

Dim rng As Range, cell As Range
Set rng = Selection

For Each cell In rng
If cell.Locked = True Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
Next cell

End If
End Sub

 

[Andrew Poulsom]

You can only use Undo to undo the Clear command in its entirety. You can't limit it to particular cells.

 

[Craig__]

I have experimented a bit more and found that the code below sort of works but not totally reliable. Seems to work on some columns but not others. Not sure why. Would be grateful if someone might be able to develop it a bit further.


Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Selection, Columns("A:G")) Is Nothing Then

Application.OnKey "{DELETE}", "Locked_Cells_Check"
End If

End Sub

Sub Locked_Cells_Check()
Dim rng As Range, cell As Range
Set rng = Selection

For Each cell In rng
If cell.Locked = False Then
cell.ClearContents
End If
Next cell

Application.OnKey "{DELETE}" 'Return Del key to normal state
End Sub

 

[Andrew Poulsom]

The Worksheet_Change event fires after the user presses the Delete key. So it may be too late to set up the OnKey.

 

[Craig__]

Many thanks for your advice on this Andrew. So looks like the OnKey approach is also a no no. Back to the drawing board.

 

[Craig__]

Finally, I got it work with this:

Private Sub Worksheet_Activate()
Application.OnKey "{DELETE}", "Locked_Cells_Check"
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "{DELETE}"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "{DELETE}"
End Sub

Sub Locked_Cells_Check()
Dim rng As Range, cell As Range
Set rng = Selection

For Each cell In rng
If cell.Locked = False Then
cell.ClearContents
End If
Next cell
End Sub

 

[Andrew Poulsom]

Isn't clearing the contents of the cells what has already happened when the Delete key was pressed?

 

[Craig__]

Only the contents of unlocked cells within selection are cleared.
Contents of any locked cells within selection are not cleared.
Which is what I need.

 

[Andrew Poulsom]

You code clears the locked cells within the selection. But the user has pressed Delete, so aren't all the cells already cleared?