Turn off copy in VBA

• • Reply

  18 years ago

  by Gravitycure

  gravitycure

  England, United Kingdom

  Joined 19 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?
  

  
  Cheers

  Report abuse
• • Reply

  18 years ago

  by Nigorr

  Nicholas Orr

  Brisbane, Australia, Australia

  Joined 19 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?
  

  
  Nick

  Report abuse
• • Reply

  18 years ago

  by Gravitycure

  gravitycure

  England, United Kingdom

  Joined 19 years ago

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

  Report abuse
• • Reply

  18 years ago

  by Thorny

  K. R. Thorson

  Florida, USA, United States

  Joined 19 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
  

  
  

  Code:

  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.

  Report abuse
• • Reply

  18 years ago

  by Nigorr

  Nicholas Orr

  Brisbane, Australia, Australia

  Joined 19 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.

  Report abuse
• • Reply

  18 years ago

  by Thorny

  K. R. Thorson

  Florida, USA, United States

  Joined 19 years ago

  Nigorr
  

  
  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.
  

  Report abuse
• • Reply

  18 years ago

  by Gravitycure

  gravitycure

  England, United Kingdom

  Joined 19 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!

  Report abuse
• • Reply

  18 years ago

  by Thorny

  K. R. Thorson

  Florida, USA, United States

  Joined 19 years ago

  Gravitycure
  

  
  

  
  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.  

  Report abuse
• • Reply

  18 years ago

  by Gravitycure

  gravitycure

  England, United Kingdom

  Joined 19 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???

  Report abuse