How to Prevent Your Excel Workbook from being Moved or Copied

Data theft can be minimised by disabling various menu and right-click features in Excel. We discuss below how to do this using VBA.

It is assumed the reader has the Excel Developer ribbon displayed, and is familiar with the VBA Editor.

The User Interface

Build a worksheet with two buttons and a little data. 

This sheet will have its ribbon displayed, and all the right_click functionality that Excel affords.

The sheet is available here.

The VBA Code

There are a variety of commandbar controls that can be disabled. You will need to search Google for ID numbers of these controls. We will use IDs 19 and 21 for this exercise.

Sub Auto_Open()    ‘Auto_open runs automatically when the workbook is opened
    Dim Control As Office.CommandBarControl

    Application.CommandBars("ply").Enabled = False     'disables right-Click on sheet tab

    For Each Control In Application.CommandBars.FindControls(ID:=21)    'disables CUT
        Control.Enabled = False
    Next Control

    For Each Control In Application.CommandBars.FindControls(ID:=19)     'disables COPY
        Control.Enabled = False
    Next Control
    Application.CellDragAndDrop = False
 
    Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"", False)"     'Hides ribbon
End Sub

Sub Restore()
    Application.CommandBars("ply").Enabled = True     'enables right-click on sheet tab

    Dim Control As Office.CommandBarControl

    For Each Control In Application.CommandBars.FindControls(ID:=21)     'enables CUT
        Control.Enabled = True
    Next Control

    For Each Control In Application.CommandBars.FindControls(ID:=19)     'enables COPY
        Control.Enabled = True
    Next Control
    Application.CellDragAndDrop = True

    Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"", True)"     'shows ribbon
End Sub

Sub Auto_close()    
    Call Restore    ‘Ensure other workbooks work properly
End Sub

Assign the Restore button to sub Restore, and the Disable button to sub Auto_open.

Run the Auto_open code; you will note that Cut and Copy are disabled on the right-click menu, and that the ribbon has vanished. You will also be unable to move the sheet to another workbook from the sheet tab.

There will always be ways around these precautions, but the object is to make it difficult for all but the serious hacker; protecting the spreadsheet is a further deterrent, as well as ensuring it can’t be copied directly from a sub folder.

Finally, protect your code from being viewed or changed:  Tools>Properties>Protection.

Excel File Corruption

“I have de-duped the file; now I am experiencing Excel failures for no discernible reason. However, I still expect to be finished today.”

Whether this has a happy ending or not might depend on whether or not the user has an effective tool available with which to repair Excel. In the case in point, the spreadsheet had in excess of 90,000 rows.

Author Introduction:

Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair rar and sql recovery software products. For more information visit www.datanumen.com