Copy and Paste Between Workbooks

[Lee]

This is the code I have in ThisWorkbook Object

Private Sub Workbook_Activate()
DisableCutAndPaste
End Sub

Private Sub Workbook_DeActivate()
EnableCutAndPaste
End Sub

In the module I have the following:

Sub EnableControl(Id As Integer, Enabled As Boolean)
'Works with cut/paste subs
Dim CB As CommandBar
Dim C As CommandBarControl
For Each CB In Application.CommandBars
Set C = CB.FindControl(Id:=Id, recursive:=True)
If Not C Is Nothing Then C.Enabled = Enabled
Next
End Sub

Sub EnableCutAndPaste()
'turn on cut and drag & drop capability
EnableControl 21, True ' cut
EnableControl 755, True 'pastespecial
Application.OnKey "^x" 'ctrl x cut
Application.CellDragAndDrop = True
End Sub

Sub DisableCutAndPaste()
'turn off cut and drag & drop capability
EnableControl 21, False ' cut
EnableControl 755, False 'pastespecial
Application.OnKey "^x", "" 'ctrl x cut
Application.CellDragAndDrop = False
End Sub

Everything seems to work fine. It disables the cut, paste
special, and the cell drag and drop. I am able to copy
and paste within the worksheet as I expected.

If I open another workbook all of the controls work that I
have disabled in the first workbook. If I open two other
workbooks I can cut/copy/paste/paste special between the
two other open workbooks.

What in the code stops me from copying from one of the
other open workbooks to the one that I have disabled the
cut and paste? I can comment out the sub routines shown
above and can copy from one workbook to the one with the
code. I just can't figure out what is stopping the copy
between the workbooks when these subroutines are active.

Thanks for any help

Lee

 

[Patrick Molloy]

surely when you copy from another workbook to your book with the code, as
soon as you select it the workbook Activate code kicks in and runs
DisableCutAndPaste

 

[Lee]

Patrick,

Thanks. Appreciate the response. I didn't explain the
problem enough in the earlier message.

When I invoke the DisableCutAndPaste it shuts off the
ability to cut/paste special/and cell drag and drop. It
doesn't shut off the ability to paste.

If I go to another workbook, copy some data, then try to
paste it to the one with the code, I expected the
DisableCutAndPaste to kick in and shut off the paste
special, but the problem is that paste is also greyed
out. For some reason it doesn't recognize that I have
anything to paste into the sheet (or it won't let me).

Does the Activation of a new workbook or the Application
CellDragandDrop somehow interfere with the paste control?
Or do I not understand another part of the code and it's
relationship between workbooks.

Thanks again

Lee