Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Copy and paste between workbooks | Excel Discussion (Misc queries) | |||
Can NOT copy / paste across workbooks | Excel Discussion (Misc queries) | |||
Can't Copy and Paste between Workbooks | Excel Discussion (Misc queries) | |||
Copy and paste between workbooks | Excel Discussion (Misc queries) |