Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to run a procedure before the paste command?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to run a procedure before the paste command?
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. -- Regards, Tom Ogilvy "Carl" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to run a procedure before the paste command?
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 "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Carl" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to run a procedure before the paste command?
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. -- Regards, Tom Ogilvy "Carl" wrote: 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 "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Carl" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to run a procedure before the paste command?
Thanks for the info. Good point. Carl
"Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Carl" wrote: 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 "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Carl" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to run a procedure before the paste command?
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 Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Carl" wrote: 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 "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Carl" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to run a procedure before the paste command?
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 "Carl" wrote: 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 Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Carl" wrote: 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 "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Carl" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simplify Command Button Procedure | Excel Discussion (Misc queries) | |||
Enable Command Button Procedure | Excel Discussion (Misc queries) | |||
UNIQ EXPEREINCE: PROCEDURE WORKS FINE USING F8 BUT GET ERROR WHEN COMMAND BUTTON IS CLICKED | Excel Programming | |||
How to elegantly end a main procedure from a userform command button? | Excel Programming | |||
Paste and Paste Special command are not enabled in Excel | Excel Worksheet Functions |