PDA

View Full Version : Prevent Copying Worksheets



stuartgb100
03-14-2017, 12:21 PM
Hi,

In a protected workbook, how do I stop the copying of a sheet(s) to a new workbook, please ?

Thanks.

Logit
03-14-2017, 01:23 PM
'*** In a standard module ***




Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) ' pastespecial

'Activate/deactivate drag and drop ability
Application.CellDragAndDrop = Allow

'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
With Application
Select Case Allow
Case Is = False
.OnKey "^c", "CutCopyPasteDisabled"
.OnKey "^v", "CutCopyPasteDisabled"
.OnKey "^x", "CutCopyPasteDisabled"
.OnKey "+{DEL}", "CutCopyPasteDisabled"
.OnKey "^{INSERT}", "CutCopyPasteDisabled"
Case Is = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
.OnKey "+{DEL}"
.OnKey "^{INSERT}"
End Select
End With
End Sub

Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
'Activate/Deactivate specific menu item
Dim cBar As CommandBar
Dim cBarCtrl As CommandBarControl
For Each cBar In Application.CommandBars
If cBar.Name <> "Clipboard" Then
Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
End If
Next
End Sub

Sub CutCopyPasteDisabled()
'Inform user that the functions have been disabled
MsgBox "Sorry! Cutting, copying and pasting have been disabled in this workbook!"
End Sub




'*** In the ThisWorkbook Module ***




Option Explicit

Private Sub Workbook_Activate()
Call ToggleCutCopyAndPaste(False)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call ToggleCutCopyAndPaste(True)
End Sub

Private Sub Workbook_Deactivate()
Call ToggleCutCopyAndPaste(True)
End Sub

Private Sub Workbook_Open()
Call ToggleCutCopyAndPaste(False)
End Sub

mdmackillop
03-14-2017, 02:56 PM
http://www.vbaexpress.com/kb/getarticle.php?kb_id=373

Logit
03-14-2017, 03:05 PM
Echo ?

:haha:

Paul_Hossler
03-14-2017, 04:00 PM
Hi,

In a protected workbook, how do I stop the copying of a sheet(s) to a new workbook, please ?

Thanks.

Q: do you mean copying a Worksheet to another workbook (Worksheets("Sheet1").Copy) or copying/pasting cells between workbooks?

stuartgb100
03-15-2017, 12:15 AM
Q: do you mean copying a Worksheet to another workbook (Worksheets("Sheet1").Copy) or copying/pasting cells between workbooks?

Thanks all for the response.

I'm trying to stop a user from creating their own copy of my workbook.

I have a project distributed as a template.
They open the template and are 'forced' to enable macros.
They are then 'forced' to save as a new macro-enabled workbook.
Assuming they enable macros, all is fine.

Using the template method allows me to make improvements to the code as situations arise, or to add new features as and when. I then issue the new template.

however, some users have simply been making a copy of their workbook, editing it to remove previous data, and then carrying on as if it were a new book.

This means they are avoiding the new template.

In the new template code, I store in a hidden sheet the name the user chooses for the workbook they create. I check that name in the open event. If it's different the workbook closes.
So if user makes a copy of the file via file explorer etc (and changes the filename) then it will close.

I'm now trying to stop someone from constructing their own book from scratch.

Seems to me they could open a new xlsm workbook and then copy/move sheets into the new book ?

Thanks.

stuartgb100
03-15-2017, 12:14 PM
Just to be clear then, the immediate issue is this:

When a workbook is open, how do I stop the move/copy option of a worksheet to another workbook,
but allow move/copy sheets within the parent workbook ?

Thanks.

rlv
03-15-2017, 08:17 PM
I've faced a similar situation with regards to templates being deployed to a large user group and I've yet to come across a perfect answer so I'll be interested to see if anyone here has a clever idea. The choice usually comes down to using various nagging mechanisms and voluntary compliance, or else trying to enforce things via excel's protection mechanisms. Protecting the workbook structure will block copying to another workbook. What is the downside of accepting the tradeoff of also blocking move/copy within the parent workbook? If your template is well designed, you should be able to minimize the need for the user to create new worksheets. And you can still provide a programmatic method of adding a new sheet if that seems warranted.

mikerickson
03-16-2017, 11:15 AM
Woud putting this in the ThisWorkbook module of the workbook do what you want?
One can copy paste within the book, or copy from another source to the book, but not from the book to another workbook.

Private Sub Workbook_Deactivate()
Application.CutCopyMode = False
End Sub

(This does not prevent SaveAs)

BTW, Excel is not a secure platform. AFAIK, there is no way to securely protect your worksheets from being used as the user wants them to. (There are trivially simple ways to copy your data that can't be discussed on the forum.) Your effort might be better spent on how to get the updates to the copied workbooks rather than preventing their copying. If the user wants a copy, make it so the user gets what they want. Remember the coder is the servant of the user, not the reverse.

Logit
03-16-2017, 11:23 AM
Remember the coder is the servant of the user, not the reverse.

WHAT !!!!???? :eek:

OMG ! :motz2:

(Thank you for that. Brightened my day ... I think ?)