Workbook_Deactivate() clears clipboard

Question

I have a workbook with some VBA code in it to control appearance, protection, and cut/copy/paste, along with some other items.  I have it set up to reset on deactivating the workbook, but some of the code is clearing the clipboard when going from one workbook to another.  It does not clear when going to Word, Notepad, etc.  I don't want it to clear when going to another workbook.  Here is the code.  The commented lines are the ones causing the problems.

Private Sub Workbook_Deactivate()

        Application.DisplayFullScreen = False

'        Application.CellDragAndDrop = True

        Application.DisplayPasteOptions = True

        Application.DisplayInsertOptions = True

'        Application.DisplayFormulaBar = True

'        Application.DisplayCommentIndicator = 1

'        Application.DisplayStatusBar = True

        Application.EnableAutoComplete = True

'        Application.MoveAfterReturn = True

'        Application.MoveAfterReturnDirection = xlDown

        Application.OnKey "^v"

End Sub

I can't understand why any other than drag and drop would have any effect on pasting to another workbook, but if I uncomment any of those that are commented now, I can't paste to another workbook.  I need this to work with several versions and operating systems.

Brad

Excel XP - 2010

Windows XP (32 bit) - 7 (64 bit)

Answer 1

There is not the slightest need to use this code when you run a macro. Refer the cells in the different workbooks as you like and copy them as you like. Here is an example:

Sub Test()

  Dim Source As Range, Dest As Range

  Set Source = ThisWorkbook.Sheets("Sheet1").Range("A1:A2")

  Set Dest = Workbooks("File.xls").Sheets("qwe").Range("B2")

  'Usual copy

  Source.Copy Dest

  'PasteSpecial

  Source.Copy

  Dest.PasteSpecial xlPasteValues

  'This shows how to get the selection from an other workbook

  'Assume that ThisWorkbook is the active workbook

  Set Source = Workbooks("File1.xls").Windows(1).Selection

  Set Dest = ThisWorkbook.Sheets(1).Range("A1")

  Source.Copy Dest

End Sub

And which Paste-Method is used is not related to the Copy/Cut method. If the Application.CutCopyMode is in affect, the user can paste as he like, e.g. right click a cell and choose a paste method.

BTW, you have to use Workbook_SheetSelectionChange event too, to keep LastSelectionup up to date, because Workbook_Activate is executed only once.

Andreas.

Answer 2

Due to issues with AutoCAD, I have changed my approach.  I had problems with AutoCAD copying the wrong selection from Excel if the selected area was not on the active sheet.  In doing this and with the other changes we made, it now works the way I want.

Thank you for your help.

Brad

Answer 3

This is closer to what I need; however, the copy command is in a routine that copies the table from another worksheet and then returns to the input worksheet.  When I run your code, it copies the current cell in the input worksheet, not the output table. 

I also have run across another issue.  I would like the user to paste special - values and not a simple pasting equations that link back to the original file.  Is there a way to set up the copy command so that it is copying values so that the user doesn't have to know to paste special - values?  I am sort of thinking to have a copy - paste special value into a different part of the worksheet, and then copying the new array.  Is there a better way?

Here is the full code.

Option Explicit

Dim LastSelection As Object

---

Private Sub Workbook_Activate()

    Application.EnableEvents = True

    With Application

        .DisplayFullScreen = True

        .CellDragAndDrop = False

        .DisplayPasteOptions = False

        .DisplayInsertOptions = False

        .DisplayFormulaBar = False

        .DisplayStatusBar = False

        .DisplayCommentIndicator = 0

'        .OnKey "^v", "MyPasteSpecial"

        .EnableAutoComplete = False

        .MoveAfterReturn = True

        .MoveAfterReturnDirection = xlDown

    End With

    With ActiveWindow

        .DisplayHeadings = False

        .DisplayHorizontalScrollBar = True

        .DisplayVerticalScrollBar = True

        .DisplayWorkbookTabs = False

        .DisplayGridlines = False

        .DisplayZeros = True

    End With

  Set LastSelection = Selection

End Sub

---

Private Sub Workbook_Deactivate()

Dim CutCopyMode As XlCutCopyMode

  CutCopyMode = Application.CutCopyMode

        Application.DisplayFullScreen = False

        Application.CellDragAndDrop = True

        Application.DisplayPasteOptions = True

        Application.DisplayInsertOptions = True

        Application.DisplayFormulaBar = True

        Application.DisplayCommentIndicator = 1

        Application.DisplayStatusBar = True

        Application.EnableAutoComplete = True

        Application.MoveAfterReturn = True

        Application.MoveAfterReturnDirection = xlDown

        Application.OnKey "^v"

 Application.DisplayFormulaBar = True

  If CutCopyMode <> Application.CutCopyMode Then

    Select Case CutCopyMode = xlCopy

      Case xlCopy

        LastSelection.Copy

      Case xlCut

        LastSelection.Cut

    End Select

  End If

End Sub

Brad

Answer 4

Try something like this.

Andreas.

Option Explicit

Dim LastSelection As Object

Private Sub Workbook_Activate()

  Set LastSelection = Selection

End Sub

Private Sub Workbook_Deactivate()

  Dim CutCopyMode As XlCutCopyMode

  CutCopyMode = Application.CutCopyMode

  'your code here

  Application.DisplayFormulaBar = True

  If CutCopyMode <> Application.CutCopyMode Then

    Select Case CutCopyMode = xlCopy

      Case xlCopy

        LastSelection.Copy

      Case xlCut

        LastSelection.Cut

    End Select

  End If

End Sub