Turn off copy in VBA

  • 18 years ago

    Does any one know if it is possible to 'turn off' copy in Excel, using VBA.  So that someone could view the sheet, but not then be able to copy it or any part of it to another sheet, or workbook?


  • 18 years ago

    The only way I would think that you can do this is to lock the workbook.

    But that is probably no what you want to do is it?


  • 18 years ago

    If you protect the workbook, you can still copy from it (which makes locking a workbook fairly pointless).  Oh well, nevermind!

  • 18 years ago

    Here is some code that will prevent anyone selecting something on a worksheet and some additional code that as a example of how to turn off any menu item you do not want someone to have access to.(EXCEL 2000).  However,  I'm still not sure how to prevent someone from breaking out of the Worksheet .open routine.

    Put in the Workbook section

    Private HostApp As Object

    Private Sub Workbook_Deactivate()
       Dim barhelp2 As Office.CommandBars
       Set barhelp2 = Application.CommandBars
       barhelp2("Edit").Enabled = trie
       barhelp2("File").Controls("&Save").Enabled = True
       barhelp2("File").Controls("Save &As...").Enabled = True
       barhelp2("Ply").Controls("&Move or Copy...").Enabled = True

    End Sub

    Private Sub Workbook_Open()
        Set HostApp = Application
        Dim barhelp2 As Office.CommandBars
        Set barhelp2 = Application.CommandBars
    '  This code is useful for finding the names of commandbars
    '  and popup menu selection names (or numbers if you prefer)

    '    icount = barhelp2.Count
    '    For iindex = 1 To 52
    '        Dim barhelp3 As Office.CommandBar
    '        Set barhelp3 = barhelp2(iindex)
    '        icount2 = barhelp3.Controls.Count
    '        For jindex = 1 To icount2
    '            Debug.Print jindex, barhelp3.Controls(jindex).Caption, iindex, _
    '                         barhelp2(iindex).Name
    '        Next
    '        Set barhelp3 = Nothing
    '    Next
    ' Turn of those option you don't want someone to have

       barhelp2("Edit").Enabled = False
       barhelp2("File").Controls("&Save").Enabled = False
       barhelp2("File").Controls("Save &As...").Enabled = False
       barhelp2("Ply").Controls("&Move or Copy...").Enabled = False

    'This example sets worksheet one so that nothing on it can be selected.

       With Worksheets(1)
           .EnableSelection = xlNoSelection
           .Protect Contents:=True, UserInterfaceOnly:=True
       End With
    End Sub

    A start but not there yet.

  • 18 years ago

    When some one selects a cell you automatically select an empty cell??

    What is it you are doing exactly??

    I'm sure you would still be able to use CTRL+C to copy even though the menu items are disabled... Also there is the context menu of right click.

  • 18 years ago


    If you have run code you will have noted that when you right click and copy, the cursor is moved to an empty cell.  Hence, it won't let you copy it.  And you have to be able to select a cell before you can use Ctrl C.  

    Other than turning off commands such as 'Save', it is EXCEL commands  'EnableSelection' and 'Protect' that do all the work.

  • 18 years ago

    Discovered that if you put the following code into WorksheetSelectionChange and WorkBookWindowDeactivate

            Application.CutCopyMode = False

    then you can't copy within Excel ie. to another cell or another workbook, although you can still copy to MS Word.

    Really could do with a Clipboard object, but there isn't one!

    Mind you, with all of this VBA, it'd be useless if the person that you are trying to stop copying your workbook just didn't enable macros!

  • 18 years ago


    Question?  Are you trying to prevent copying from your worksheet or changing your worksheet.  Are you trying to prevent printing?

    Can one fool other applications into not recognizing the spreadsheet as an object or as a file that can be loaded by that app?

    I like a challenge, they're fun.  

  • 18 years ago

    This copy prevention is not for me, but someone has asked me if it is possible.  Not too sure why they wanna do it, but they just said that they want people to be able to view the data, but not be able to copy it.  Seems like a strange request to me, as if they really wanted to copy it, they could write it all down or screen grab it even if copy was turned off.

    Who knows???

Post a reply

Enter your message below

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Measuring programming progress by lines of code is like measuring aircraft building progress by weight.” - Bill Gates