Prevent copying of protected sheets.

  • Thread starter Thread starter Vanessa
  • Start date Start date
V

Vanessa

Does anyone know how to stop users from copying and
pasting the formats from a protected worksheet to another
workbook/worksheet? Thank you
 
Hi Vanessa,

Data/formula copying & pasting is usually more of a
problem than formats, and preventing the copying & pasting
of formulae is fairly straightforward.

To prevent format copying & pasting, you'll need a macro-
based solution, but that could be defeated by the user
disabling macros when the workbook is loaded.

Now that the homily is over, here's a macro-based solution
that stops cutting, copying and pasting:

Sub DisableCutAndPaste()
EnableControl 21, False ' cut
EnableControl 19, False ' copy
EnableControl 22, False ' paste
EnableControl 755, False ' pastespecial
Application.OnKey "^c", ""
Application.OnKey "^v", ""
Application.OnKey "+{DEL}", ""
Application.OnKey "+{INSERT}", ""
Application.CellDragAndDrop = False
End Sub

Sub EnableCutAndPaste()
EnableControl 21, True ' cut
EnableControl 19, True ' copy
EnableControl 22, True ' paste
EnableControl 755, True ' pastespecial
Application.OnKey "^c"
Application.OnKey "^v"
Application.OnKey "+{DEL}"
Application.OnKey "+{INSERT}"
Application.CellDragAndDrop = True
End Sub

Sub EnableControl(Id As Integer, Enabled As Boolean)
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

To use, run DisableCutAndPaste from a suitable event
procedure (e.g. Workbook_Open or Worksheet_Activate) and
EnableCutAndPaste from another (e.g. Workbook_Close or
Worksheet_Deactivate).

Cheers
PS: Note that the macro prevents all cutting, copying and
pasting of data, formulae and formats.
 
This would be very easy to overcome, but when you protect the sheet, do not
allow any cell selection.
 
Back
Top