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

Copy and Paste Between Workbooks
 

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

--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
"Lee" wrote in message
...
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

Copy and Paste Between Workbooks
 

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


-----Original Message-----
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

--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
"Lee" wrote in
message
...
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


.