View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Lee Lee is offline
external usenet poster
 
Posts: 33
Default Copy and Paste Between Workbooks

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