Results 1 to 8 of 8

Thread: Code sometimes not working on Workbook_SheetChange
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2016
    Posts
    153
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Code sometimes not working on Workbook_SheetChange

    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.

    Code:
    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?

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,041
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Code sometimes not working on Workbook_SheetChange

    Where have you placed this code?
    In which module?
    It needs to be in the "ThisWorkbook" module.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Aug 2016
    Posts
    153
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code sometimes not working on Workbook_SheetChange

    Yes, I have it in the ThisWorkbook module.

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,590
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Code sometimes not working on Workbook_SheetChange

    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:

    Code:
    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
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #5
    Board Regular
    Join Date
    Aug 2016
    Posts
    153
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code sometimes not working on Workbook_SheetChange

    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?

  6. #6
    Board Regular
    Join Date
    Aug 2016
    Posts
    153
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code sometimes not working on Workbook_SheetChange

    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.
    Last edited by mcomp72; Sep 27th, 2019 at 01:13 PM.

  7. #7
    Board Regular
    Join Date
    Aug 2016
    Posts
    153
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code sometimes not working on Workbook_SheetChange

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

    Code:
    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?
    Last edited by mcomp72; Sep 27th, 2019 at 01:26 PM.

  8. #8
    Board Regular
    Join Date
    Aug 2016
    Posts
    153
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code sometimes not working on Workbook_SheetChange

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •