Prevent deleting multiple cells at once

[Horatio J. Bilge, Jr.]

How can I prevent a user from deleting more than one cell at a time? I have
code that returns the cell's original contents if a user presses delete, but
if they highlight several cells and press delete, it doesn't work (the cells'
contents are actually deleted). I added a message box to the code as below,
but it doesn't actually prevent them from deleting the cells' contents.

If Target.Cells.Count > 1 Then
MsgBox("You can only edit one cell at a time.")
Exit Sub
End If

Thanks,
~ Horatio

 

[Otto Moehrbach]

Horatio
What you have to do is to first determine if Target.Count>1.
If it is, issue an Undo command like:
Application.EnableEvents=False
Application.Undo
Application.EnableEvents=True
This gets your values back. Now you can display the message box. The
complete macro would look like:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "Whatever"
End If
End Sub

 

[Horatio J. Bilge, Jr.]

Thanks. That works great.

Horatio
What you have to do is to first determine if Target.Count>1.
If it is, issue an Undo command like:
Application.EnableEvents=False
Application.Undo
Application.EnableEvents=True
This gets your values back. Now you can display the message box. The
complete macro would look like:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "Whatever"
End If
End Sub