Is there a way to run a procedure before the paste command?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to have a pop-up msg come up when someone tries to paste into a cell.
Is there a way to do this? I know how to create the pop-up, I just need to
know how to create the code to run when an attempt to paste is executed. I
created the following but the user has to double-click the cell and this is
not practical:

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
With Target
If Application.CutCopyMode = False Then
Cancel = True
Else
Application.Run "PasteWarning"
End If
End With
End Sub

Thanks in advance, Carl
 
If you want to prevent pasting in any cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
application.Cutcopymode = False
End Sub

but this will clear the clipboard as well. (and prevent Undo also I would
expect)

For something more discriminating, I believe you will have a hard time. For
example:
Application.CutCopyMode being true only means that there is a range in the
clipboard - it doesn't mean the user is trying to paste in that cell.

There is no event that is fired exclusively when something is pasted.
 
Thanks, Tom. Your code worked perfectly. You have helped me many times in the
past. I made a slight revision (following) to have a popup msg appear if the
user tried to paste data but they can still do so after acknowledging the
msg. I did not want to totally restrict all pasting (although you answered my
original question perfectly). Thanks again, Carl

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = False Then
Cancel = True
Else
Application.Run "PopupMsgDoNotCopyPaste"
End If
End Sub
 
Just for information, Cancel is meaningless in the SelectionChange event.
Cancel is only meaingful in Events that have a name including the word
"Before . . ." and then Cancel is found as an argument to the event. In
those cases, when cancel is set to true, the action that initiated the event
will be cancelled. (such as the beforedouble click; by setting cancel =
true, the user won't go into Edit mode. the default value of cancel is
false in such an event, so it doesn't need to be set to false.

Also, just note my original comments that the user can make numerous
selections before attempting to paste what has been copied, although there is
a good probability that the selection is for the purpose of pasting. If it
is not the purpose, however, your message could seem confusing to the User.
Just something to consider.
 
Thanks for the info. Good point. Carl

Tom Ogilvy said:
Just for information, Cancel is meaningless in the SelectionChange event.
Cancel is only meaingful in Events that have a name including the word
"Before . . ." and then Cancel is found as an argument to the event. In
those cases, when cancel is set to true, the action that initiated the event
will be cancelled. (such as the beforedouble click; by setting cancel =
true, the user won't go into Edit mode. the default value of cancel is
false in such an event, so it doesn't need to be set to false.

Also, just note my original comments that the user can make numerous
selections before attempting to paste what has been copied, although there is
a good probability that the selection is for the purpose of pasting. If it
is not the purpose, however, your message could seem confusing to the User.
Just something to consider.
Click to expand...
 
Thanks, Tom. If I can try your patience once more, I need to disable the
Popup msg when my WorkBook_Open code pastes info from one cell to another. I
will have my WorkBook_Open code enter a 1 in cell A1 of Sheet1 to disable. I
tried the following with no luck. Please advise, as I have tried several
variations. Thanks again, Carl

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = True And (Sheets("Sheet1").Range("A1") = 0)
Then
Application.Run "PopupMsgDoNotCopyPaste"
End If
End Sub
 
Tom, I answered my own question. The following works fine. Thanks for all the
help! Carl

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = False Or Sheets("Summary
Sheet").Range("a1") = 1 Then
Cancel = True
Else
Application.Run "PopupMsgDoNotCopyPaste"
End If
End Sub
 
Back
Top