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
Felix
Can I suggest that you rename this article to “How to prevent a worksheet within your Excel workbook from being Moved or Copied” – for VBA cannot stop even a relatively inexperienced user of Windows Explorer (or equivalent application) from moving or copying an Excel workbook file (without even launching Excel)