none
Workbook_Activate and Workbook_Deactivate Events causing Copy and Paste problem between two workbooks RRS feed

  • Question

  • Here's some details with my excel workbook... 
    I have a workbook that have Activate and Deactivate event that do the following... 
     - Enable / Disabling the Cut and Delete CommandBars / Menus
     - Configures Paste CommandBars and Ctrl + V  to xlPasteValues only 
    Now the problem I am having is when I copy the cell/range from a different workbook and then when I click or set active on my excel workbook (w/c eventually triggers the Activate Event), it lost selection focus from the previous sheet. So pasting the data is not possible... 
    Any ideas on a work around? 
    Monday, October 10, 2011 8:47 PM

Answers

  •    Application.CellDragAndDrop = False 'The dotted selected cells goes away from this line...

    Indeed it would! CellDragAndDrop = False clears Excel's clipboard as intended.

    I am thinking right now of repositioning the "Application.CellDragAndDrop = False" code on a different event or something... What do you think??

    If(?) the objective is
    A. to allow users to copy (values only) from the previously active workbook to "your" workbook why do you need that line at all, surely not.
    B. If you don't want to allow copy/paste from any other workbook CellDragAndDrop = False is exactly what you do want to do.

    I'm guessing it's A, so remove that line, when user does Ctrl-v the onkey calls your paste-values routine. What about Shift-Insert though?

    In passing should be real careful when messing with user's toolbars. I trust there's more code to ensure no way to quit Excel with those menus disabled.

    Peter Thornton

    Tuesday, October 11, 2011 7:54 AM
    Moderator
  • {snip}
    In passing should be real careful when messing with user's toolbars. I trust there's more code to ensure no way to quit Excel with those menus disabled.

    Hmm... You got me here and thanks for pointing it out... I'll insert the sub procedure to reset the commandbar to its normal setting with On BeforeClose Event. :D 






    You know your environment better than me but...

    it is not just using a BeforeClose event to 'reset' the commandbar.

    If the consumer has custom modifications to the commandbar you need to reset the *custom* modifications.

    You also have to cater to the possibility that there might be a catastrophic failure that precludes the execution of the BeforeClose routine.  Your code could crash, the consumer may 'force quit' Excel, turn off the computer by pressing the power switch, or there might be a transient loss of power that turns off the computer!

    Frankly, I find it nearly impossible to cater to every possible way in which someone can do something in Excel.  There are simply too many variants.  Some time back, a client wanted -- really, really wanted -- to disable the ability to cut/paste something other than an entire row.  We implemented that and one of his employees found a way around it.  So, we patched that and..you get the idea.  Not to mention that employees were increasingly agitated at the restrictions placed on their productivity (real or perceived it didn't matter -- the *actual* customers were unhappy).

    Finally, I suggested, and, luckily. he accepted, that we verify the integrity of the worksheet rather than monkey around trying to make the product 'idiot proof.'  By focusing on what rather than how, the employees were happy and we could ensure the integrity of the data.

     


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Tuesday, October 11, 2011 5:44 PM
  • I don't see how CellDragAndDrop = False is going to help, unless of course the objective is to prevent copying from another workbook.

    I guess Shift-Insert also calls the built-in Ctrl+V procedure or actions.

    No, I think you must you're missing something there.

    I'll insert the sub procedure to reset the commandbar to its normal setting with On BeforeClose Event. :D

    I take it you mean calling your re-enable routine rather than actually "resetting" user's toolbars, at least I hope so. As Tushar mentioned there are all sorts of reasons why the BeforeClose event might not get called, also it can be called and the user subsequently change his mind about closing Excel or the workbook (cancel in save changes).

    Also as Tushar mentioned there's a lot you can do to prevent a user making unwanted changes. If he can paste he can also type a change, which makes disabling paste rather pointless, other than to minimise accidental changes.

    I've no idea about your specific workbook but typically best to lock down everything other than cells user is required to input data. And even in those cells you might have either built in cell validation or your own in a worksheet change event to ensure inputted data is within expected constraints

    Peter Thornton

    Tuesday, October 11, 2011 10:19 PM
    Moderator

All replies

  • You problem is much more fundamental.  Nearly any macro can affect Copy/Paste.  Say we have a simple macro:

     

    Sub dural()
    MsgBox "Hello"
    End Sub

     

    1. Click on a cell and CNTRL-c
    2. Run the macro
    3. Click on another cell in the same sheet and CNTRL-v

    The Paste will fail!

     

    (In fact, you can omit the MsgBox and the Paste will still fail.)

     

     


    gsnu201109
    Monday, October 10, 2011 9:48 PM
    Moderator
  • GS, normally simply showing a Msgbox wouldn't result in losing the clipboard, should even be able to activate different sheets, change commandbars etc. However say changing say cells, shapes etc with code and the clipboard would certainly be lost.

    Frequently in code I do something like this to preserve the clipboard if possible

    If cel.value <> myValue then cel.value = myValue

    Marteo, something you are doing in your code is causing the clipboard to be cleared, you haven't shown your code so we can't say. Try this, copy some cells. Size and position relevant windows so you can step through your code with F8 and watch the dotted lines around the cells you copied. When the lines disappear that's the line of code that cleared the clipboard.

    Pete Thornton

    Monday, October 10, 2011 10:08 PM
    Moderator
  • Hi Peter... 

    I have currently have two temporary workarounds with the issue. 1) is combining both workbooks 2) and using the Office Clipboard when copying & pasting between two workbooks. 

    So eventually, doesn't really clear up the clipboard. When I am copying a range of cells from a different worksheet. I  have the dotted lines on the cells  selections indicating that it is copied/cut. Now at the moment I click on the coded template, the dotted cell selected goes away...   Eventually, the "Workbook Activate Event" triggers the lost of cell selection (dotted) from the other workbook. 

    Some how, I have an idea on a script of evaluating that their is current copied selection outside the workbook. If true... Maybe we can temporary set the cell selection / range to a variable. Or Maybe we can directly get the data from the clipboard using a vba code. Well... unfortunately I not well versed in coding with the clipboard... So I still reading VBA references about it... 

     

    Monday, October 10, 2011 11:17 PM
  • Hmm... Hey Pete... I took your advise of debugging each code step. And I found out that the its not the "Workbook Activate Event" triggering the problem. See code below... 
    ThisWorkbook (Code)
    Private Sub Workbook_Activate()
        disableXLMenu
    End Sub
    
    Private Sub Workbook_Deactivate()
        enableXLMenu
    End Sub
    
    Module1 (Code)
    Public Sub enableXLMenu()
        Application.CellDragAndDrop = True 
        Dim Ctrl As Office.CommandBarControl
        For Each Ctrl In Application.CommandBars.FindControls(ID:=21) 'cut
            Ctrl.Enabled = True
        Next Ctrl
        For Each Ctrl In Application.CommandBars.FindControls(ID:=22) 'paste
            Ctrl.Enabled = True
        Next Ctrl
        For Each Ctrl In Application.CommandBars.FindControls(ID:=478) 'delete
            Ctrl.Enabled = True
        Next Ctrl
            For Each Ctrl In Application.CommandBars.FindControls(ID:=292) 'right click delete
            Ctrl.Enabled = True
        Next Ctrl
        Application.OnKey "^{x}"
        Application.OnKey "+{delete}"
        Application.OnKey "^{v}"
    End Sub
    Public Sub disableXLMenu()
        Application.CellDragAndDrop = False 'The dotted selected cells goes away from this line... 
        Dim Ctrl As Office.CommandBarControl
        For Each Ctrl In Application.CommandBars.FindControls(ID:=21) 'cut
            Ctrl.Enabled = False
        Next Ctrl
        For Each Ctrl In Application.CommandBars.FindControls(ID:=22) 'paste
            Ctrl.Enabled = False
        Next Ctrl
        For Each Ctrl In Application.CommandBars.FindControls(ID:=478) 'delete
            Ctrl.Enabled = False
        Next Ctrl
        For Each Ctrl In Application.CommandBars.FindControls(ID:=292) 'right click delete
            Ctrl.Enabled = False
        Next Ctrl
    
        Application.OnKey "^{x}", "msgboxDisableCut"
        Application.OnKey "+{delete}", "msgboxDisableCut"
        Application.OnKey "^{v}", "PasteValues"
        
    End Sub
    
    Public Sub msgboxDisableCut()
        MsgBox "Keyboard Shortcut is disable [Ctrl + X] on this tool. Please use Copy [Ctrl + C] instead.", vbInformation + vbOKOnly
    End Sub
    Sub PasteValues()
    ' Keyboard Shortcut: Ctrl+v
        On Error Resume Next
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    End Sub
    
    I am thinking right now of repositioning the "A
    pplication.CellDragAndDrop = False" code on a different event or something... What do you think??
    Monday, October 10, 2011 11:30 PM
  •    Application.CellDragAndDrop = False 'The dotted selected cells goes away from this line...

    Indeed it would! CellDragAndDrop = False clears Excel's clipboard as intended.

    I am thinking right now of repositioning the "Application.CellDragAndDrop = False" code on a different event or something... What do you think??

    If(?) the objective is
    A. to allow users to copy (values only) from the previously active workbook to "your" workbook why do you need that line at all, surely not.
    B. If you don't want to allow copy/paste from any other workbook CellDragAndDrop = False is exactly what you do want to do.

    I'm guessing it's A, so remove that line, when user does Ctrl-v the onkey calls your paste-values routine. What about Shift-Insert though?

    In passing should be real careful when messing with user's toolbars. I trust there's more code to ensure no way to quit Excel with those menus disabled.

    Peter Thornton

    Tuesday, October 11, 2011 7:54 AM
    Moderator
  • If(?) the objective is A. to allow users to copy (values only) from the previously active workbook to "your" workbook why do you need that line at all, surely not. B. If you don't want to allow copy/paste from any other workbook CellDragAndDrop = False is exactly what you do want to do. I'm guessing it's A, so remove that line, when user does Ctrl-v the onkey calls your paste-values routine. What about Shift-Insert though?

    Actually I just want to make sure my template to be "dummy" proof. I have some experiences some co-workers / boss that messed up cell formula's by using the cell's drag and drop features and with the fill handle.  As of now, the line is temporary commented out. But I am still trying to find a way to enable this line without causing issues with copy/paste from a different sheet.  Let me know if you have some ideas. 

    In regards to Shift-Insert... I tried hitting on those shortcut keys and for some weird reason its also pastes with values only. I guess Shift-Insert also calls the built-in Ctrl+V procedure or actions. 

    In passing should be real careful when messing with user's toolbars. I trust there's more code to ensure no way to quit Excel with those menus disabled.

    Hmm... You got me here and thanks for pointing it out... I'll insert the sub procedure to reset the commandbar to its normal setting with On BeforeClose Event. :D 





    Tuesday, October 11, 2011 4:14 PM
  • {snip}
    In passing should be real careful when messing with user's toolbars. I trust there's more code to ensure no way to quit Excel with those menus disabled.

    Hmm... You got me here and thanks for pointing it out... I'll insert the sub procedure to reset the commandbar to its normal setting with On BeforeClose Event. :D 






    You know your environment better than me but...

    it is not just using a BeforeClose event to 'reset' the commandbar.

    If the consumer has custom modifications to the commandbar you need to reset the *custom* modifications.

    You also have to cater to the possibility that there might be a catastrophic failure that precludes the execution of the BeforeClose routine.  Your code could crash, the consumer may 'force quit' Excel, turn off the computer by pressing the power switch, or there might be a transient loss of power that turns off the computer!

    Frankly, I find it nearly impossible to cater to every possible way in which someone can do something in Excel.  There are simply too many variants.  Some time back, a client wanted -- really, really wanted -- to disable the ability to cut/paste something other than an entire row.  We implemented that and one of his employees found a way around it.  So, we patched that and..you get the idea.  Not to mention that employees were increasingly agitated at the restrictions placed on their productivity (real or perceived it didn't matter -- the *actual* customers were unhappy).

    Finally, I suggested, and, luckily. he accepted, that we verify the integrity of the worksheet rather than monkey around trying to make the product 'idiot proof.'  By focusing on what rather than how, the employees were happy and we could ensure the integrity of the data.

     


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Tuesday, October 11, 2011 5:44 PM
  • I don't see how CellDragAndDrop = False is going to help, unless of course the objective is to prevent copying from another workbook.

    I guess Shift-Insert also calls the built-in Ctrl+V procedure or actions.

    No, I think you must you're missing something there.

    I'll insert the sub procedure to reset the commandbar to its normal setting with On BeforeClose Event. :D

    I take it you mean calling your re-enable routine rather than actually "resetting" user's toolbars, at least I hope so. As Tushar mentioned there are all sorts of reasons why the BeforeClose event might not get called, also it can be called and the user subsequently change his mind about closing Excel or the workbook (cancel in save changes).

    Also as Tushar mentioned there's a lot you can do to prevent a user making unwanted changes. If he can paste he can also type a change, which makes disabling paste rather pointless, other than to minimise accidental changes.

    I've no idea about your specific workbook but typically best to lock down everything other than cells user is required to input data. And even in those cells you might have either built in cell validation or your own in a worksheet change event to ensure inputted data is within expected constraints

    Peter Thornton

    Tuesday, October 11, 2011 10:19 PM
    Moderator
  • @Pete and Tushar, 

    Thanks for sharing your ideas and advise... Pretty much, the issue on this topic is already resolved. The worksheet is currently deployed to my team and we use it to upload daily transaction data to a database. Soon I'll be creating a dashboard worksheet to organize and calculate data that we need for reports. So till then, I might post another question on the forum... So see you guys soon...

    Marty  

    Thursday, October 13, 2011 7:10 PM
  • Hi Marty,

    Can you help me to solve this issue. Currently I am facing the same issue with Workbook_Deactive event. I am clearing the OnKey procedures which i have attached at Workbook_activate.

    It is causing the other workbook fail on the OnKey press.

    Workbook_activate is having below line

    this.Application.ActiveWorkbook.Application.OnKey("^z","CustomUndo" );

    Workbook_Deactive is having below line.

    this.Application.ActiveWorkbook.Application.OnKey("^z" );

    Now Ctrl+Z is not working on the other workbbook where it is not require any logic and normal ctrl+z should work.

    This issue is exists when two workbooks are attached to the same application. (On pressing Alt + F11, i am seeing these two workbooks code in same VBA editor)

    Thanks in advance

    Sreenivas

    • Edited by spvarapu Tuesday, November 29, 2011 9:57 AM
    Tuesday, November 29, 2011 9:55 AM
  • Try this:

     

    Rather than clearing OnKeys on DeActivate, in each worksheet use Activate to:

    1. clear all OnKeys
    2. setup OnKeys as required by that sheet

    gsnu201111
    Tuesday, November 29, 2011 10:28 AM
    Moderator
  • I have tried this..It is failing here also.

    Also i am disabling the Undo command in my Workbook through ribbon.xml, where i have to call customized Undo.

    Is this is causing the issue in other workbook?

    Thanks

    Sreenivas


    sreenivas
    Tuesday, November 29, 2011 1:23 PM
  • Hi Marty,

    Can you help me to solve this issue. Currently I am facing the same issue with Workbook_Deactive event. I am clearing the OnKey procedures which i have attached at Workbook_activate.

    It is causing the other workbook fail on the OnKey press.

    Workbook_activate is having below line

    this.Application.ActiveWorkbook.Application.OnKey("^z","CustomUndo" );

    Workbook_Deactive is having below line.

    this.Application.ActiveWorkbook.Application.OnKey("^z" );

    Now Ctrl+Z is not working on the other workbbook where it is not require any logic and normal ctrl+z should work.

    This issue is exists when two workbooks are attached to the same application. (On pressing Alt + F11, i am seeing these two workbooks code in same VBA editor)

    Thanks in advance

    Sreenivas

    Hey! How is it going? Sorry it took me to a while to reply...

    I think this (underlined) whats causing your issue...

     this.Application.ActiveWorkbook.Application.OnKey("^z","CustomUndo" );

    and

    this.Application.ActiveWorkbook.Application.OnKey("^z" );

    simplify your Excel Objects by using the following codes instead.

    Application.OnKey("^z","CustomUndo" );

    and

    Application.OnKey("^z" );
    You may also want to debug step by step each line by using F8 to identify causing the issue. :) I hope this helps and I am not too late...


    Marty Cosme

    Wednesday, February 8, 2012 4:18 PM
  • old post, but had the same issue and found a nice solution.

    check for Application.CutCopyMode = 1 or 2 (copy or cut is active)

    When 1 or 2 then don't use the CellDragAndDrop in the workbook_deactivate.

    Also use the CellDragAndDrop in the BeforeClose so that everything works fine when you exit the workbook.

    Private Sub Workbook_Deactivate()

          If Application.CutCopyMode = 0 Then  
            Application.CellDragAndDrop = True
          End If

    end Sub

    Wednesday, February 14, 2018 12:34 PM