Prevent deleting multiple cells at once
 

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

Prevent deleting multiple cells at once
 

Horatio
What you have to do is to first determine if Target.Count1.
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." wrote in
message ...
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

Prevent deleting multiple cells at once
 

Thanks. That works great.


"Otto Moehrbach" wrote:

Horatio
What you have to do is to first determine if Target.Count1.
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." wrote in
message ...
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