Code sometimes not working on Workbook_SheetChange

[mcomp72]

I am trying to make it so if the user copies & pastes anything in my workbook, it will only do a Paste Values. I found an answer to a post on Stack Exchange from a few ago that recommending putting the following code in the Workbook_SheetChange sub.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Application.CutCopyMode = xlCopy Then

    Application.EnableEvents = False
    Application.Undo
    Target.PasteSpecial Paste:=xlPasteValues
    Application.EnableEvents = True
    
End If

End Sub

The problem is, on certain sheets, it doesn't seem to run. If I press Ctrl-C with a cell selected on one worksheet within my workbook, then change to another sheet in my workbook, and then press Ctrl-V in a cell there, it doesn't paste only the values -- it does a normal paste. I don't understand why this is happening on certain sheets but on others it doesn't. Any ideas what might be causing this?

 

[Joe4]

Where have you placed this code?
In which module?
It needs to be in the "ThisWorkbook" module.

 

[mcomp72]

Yes, I have it in the ThisWorkbook module.

 

[JoeMo]

In instances where ctrl-V does a normal paste, are you certain that events are enabled when you do the paste? If events have inadvertently been disabled prior to the paste, ctrl-V will result in a normal paste as your code will not be triggered. Here's a modification that might help you diagnose the problem:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

MsgBox "Events are enabled"

If Application.CutCopyMode = xlCopy Then

    Application.EnableEvents = False
    Application.Undo
    Target.PasteSpecial Paste:=xlPasteValues
    Application.EnableEvents = True
    
End If

End Sub

If you don't see the message box after a ctrl-V, events are disabled. To enable them again go to the Immediate Window and type:
Application.EnableEvents = True
and press Enter

 

[mcomp72]

Hi Joe. Very interesting. I added that line of code, and you are correct... I am not seeing the MsgBox when I press Ctrl-V.

I searched my entire project in the VBA window for "Application.EnableEvents" to see what other code I have that might be disabling events. Other than the two lines in this sub (Workbook_SheetChange), it did not find it anywhere. So I am not sure what would be disabling events in my workbook. Is there some other code that has the ability to disable events? Or are events disabled by default in Excel?

 

[mcomp72]

Okay, so I did what you said and put Application.EnableEvents = True in the Immediate Window. That enabled events, because I has setup a Watch for it, so I could see that it had changed to True. BUT, then I tried a copy & paste again. I copied a cell from Sheet1 to Sheet2. I hit Ctrl-V and I received the message "Events are enabled". But then it did the paste but as a complete paste, not as Paste Values only. I'm at a loss as to what is going on.

Update: it looks like if I paste to certain pages, it does a paste special. But other pages, and it does a normal paste. For each one, I am seeing the "Events are enabled" message.

 

[mcomp72]

I just stepped through the code. In Workbook_SheetChange, when it gets to the line

If Application.CutCopyMode = xlCopy Then

it is going straight to End If. I added a watch for Application.CutCopyMode, and it turns out in some cases, it is equal to 0 (zero).

When I do the copy & paste, I am doing it the same way each time: Ctrl-C and then Ctrl-V. Ever heard of this happening?

 

[mcomp72]

UGH. I figured it out. I had some code in my workbook that was sometimes setting CutCopyMode to False. I must have added it months ago, because I forgot all about it being there!

I removed that code and now it seems to be working fine.