The goal is to keep a user from pasting to more than one cell at a time.

This is on the Worksheet_Change of a Sheet:

If Target.Cells.Count 1 Then
Selection.ClearContents
End If

I want to put a MsgBox to tell the user why the selection was cleared and
they can only paste to one cell at a time. But when I but a MsgBox after the
Selection.ClearContents it goes into a loop and the MsgBox will not close.

How do I correct this.

Thank you for your help.

Steven

Tell excel not to look for changes while your code is making a change:

if target.cells.count 1 then
application.enableevents = false
target.clearcontents
application.enableevents = true
end if

===
Although, you may want to try:

if target.cells.count 1 then
with application
.enableevents = false
.undo 'put things back the way they were, don't just clear
.enableevents = true
end with
end if



Steven wrote:

The goal is to keep a user from pasting to more than one cell at a time.

This is on the Worksheet_Change of a Sheet:

If Target.Cells.Count 1 Then
Selection.ClearContents
End If

I want to put a MsgBox to tell the user why the selection was cleared and
they can only paste to one cell at a time. But when I but a MsgBox after the
Selection.ClearContents it goes into a loop and the MsgBox will not close.

How do I correct this.

Thank you for your help.

Steven

--

Dave Peterson