Using “Strong Arm” Tactics In Excel

Category: General VBA | [Item URL]

The Excel newsgroups contain many requests for techniques that I like to call "strong arm tactics." Often, these people are looking for a way to make their workbook or add-in completely secure. Or maybe they want to take over Excel's user-interface to prevent certain actions. These questions all have one element in common:

The developer is attempting to control the end user in a way that is not really supported by Excel.

This document addresses some of these issues in the form of a questions and answers.

How can I prevent the user from copying my workbook?

The simple answer is, you can't.

When you give (or sell) an Excel workbook file, the user can do whatever they want with it, and this includes making copies. Excel does not support any type of Digital Rights Management (DRM). Any recourse that you have would need to be legal in nature. You can provide a licensing agreement that describes what is and what is not allowed. But, of course, enforcing that agreement is difficult or impossible.

How can I prevent users from using the Clipboard to copy sensitive information?

You can't. Anything that can be viewed onscreen can be copied. If your information is that sensitive, you should keep it to yourself.

How can I set things up so my Excel application doesn't look like Excel?

This is possible, to an extent, but I do not recommend it. Remember, Excel is a multi-document application. Many users like to keep several workbooks open at the same time, and your attempts to hijack the interface may not be appreciated.

That said, any solution would involve two macros:

• A Workbook_Open procedure. This is executed when the workbook is opened, and would contain code that hides all toolbars, hides the formula bar and status bar, sets up a custom menu, and so on.
• A Workbook_BeforeClose procedure, This is executed when the workbook is closed normally (i.e. Excel doesn't crash). This procedure would contain code that returns things back to normal.

If you decide to take this approach, keep in mind some potential problems:

• The VBA code to do this reliably is complex.
• There are many ways to perform actions in Excel. For example, removing the Edit - Copy menu does not disable the Ctrl+C shortcut key.
• Users can choose to open a workbook with macros disabled. If that occurs, your Workbook_Open procedure is not executed.
• Many users (myself included) actively avoid such workbooks.
• A knowledgeable user can probably undo everything your macro did.

For specific coding examples, do a search at Google Groups.

How can implement a time-limit, so my workbook becomes useless after 30 days?

Many developers have worked out a method to do this sort of thing, but it's not 100% reliable. For obvious reasons, these developers prefer to not share this knowledge in a public forum.

All of the techniques that are described here require that macros be enabled. How can I force a user to enable my macros?

You can't do it via code. Think about it... If there were a way to by-pass the user's security settings, the entire security setting feature in Excel would be rendered useless.

One common approach is to make your workbook useless unless macros are enabled. For example, you can hide all of the worksheets (using xlVeryHidden), except one (your "warning" sheet). Then protect the workbook's structure. This "warning sheet" (the single visible worksheet) would contain a message such as, "Macros are required!"

The workbook would contain a Workbook_Open procedure that unhides all of the worksheets, hides the "warning" sheet, and applies protection. Therefore, if the workbook is opened with macros enabled, the user does not see the message.

Keep in mind that this technique is very difficult to implement reliably.

Where can I find out more about protecting various items in Excel?

Try my Excel Protection FAQ tip.

Can all Excel passwords be cracked?

I don't know about all, but it does appear that the vast majority of passwords can indeed be cracked. If your workbook or add-in will be used by computer novices who have no interest in cracking your workbook, you won't have much to worry about.

Bottom line? If somebody really wants full access to a workbook or add-in, they can get it. Microsoft has never advertised Excel as a secure development environment