Disable Copy/Paste

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

How can I stop users from using Copy/Paste in any application while my macro
is running?

Or, can I use an isolated clipboard that's only available to Excel and Word
and it's not affected by what users copy in other applications?

Regards,
Richard
 
You cannot stop users from copying/pasting in other apps via a macro
in excel or word. But you can disable copy/paste in excel via macro..
see below:

The code below must be placed in the Private Module of the Workbook
Object (ThisWorkbook). To get there easily, right click on the Excel
icon, top left next to File and choose View Code. In here paste the
code below, close & save and then re-open.

Private Sub Workbook_Activate()
Dim oCtrl As Office.CommandBarControl

'Disable all Cut menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = False
Next oCtrl

'Disable all Copy menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
oCtrl.Enabled = False
Next oCtrl

Application.CellDragAndDrop = False

End Sub

Private Sub Workbook_Deactivate()
Dim oCtrl As Office.CommandBarControl

'Enable all Cut menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
oCtrl.Enabled = True
Next oCtrl

'Enable all Copy menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
oCtrl.Enabled = True
Next oCtrl

Application.CellDragAndDrop = True

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
With Application
.CellDragAndDrop = False
.CutCopyMode = False 'Clear clipboard
End With
End Sub
List of Excel 2000 CommandBar Button IDs applies to excel 97 onwards.

Found here: http://www.ozgrid.com/VBA/disable-cut-copy.htm
 
This would be really tough to disable as the copying and pasting is done not
just to MS Office programs, but for all programs on the system. Also, if
your program is ran in the background and the user is working on other
items, would you really want this disabled to the point that the user can't
do any sort of copying and pasting with other apps? I could just see how
other users would view this, so be very careful about this.

The more idea solution, which I have had to use within my production
reporting system, though also has some draw backs similar to your situation,
first avoid using the "Copy" method as "Reasonably" possible, but rather
take values directly from one cell to another cell, which works okay for
smaller ranges, but may be a time lagger for larger ranges. Once this is
done, then for those larger ranges, use the DestinationRange argument within
the Copy method of the SourceRange object. Right after that takes place,
let the CutCopyMode property on the Application object to "False".

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000
 
This doesn't account for doing the copy/cut and pasting by keyboard method.
There are 2 different keyboard combinations for each of the copying (Ctrl-C
and Ctrl-Insert), cutting (Ctrl-X and Shift-Del) and pasting (Ctrl-V and
Shift-Insert). Note, the first set of the 2 keyboard combination sets is
from the older Mac command keyboard combinations while the second set is the
older 3.1 and previous Windows keyboard combinations.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000
 
You have a good point, disabling copy/paste may not be ideal.

What about this:
Can I use an isolated clipboard that's only available to Excel and Word and
it's not affected by what users copy in other applications?
 
Back
Top