Prevent Copying Worksheets

[stuartgb100]

Hi,

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

Thanks.

[Logit]

'*** 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]

http://www.vbaexpress.com/kb/getarticle.php?kb_id=373

[Logit]

Echo ?

[Paul_Hossler]

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]

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]

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]

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]

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]

Remember the coder is the servant of the user, not the reverse.

WHAT !!!!????

OMG !

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