Sunday, May 20, 2007

Security in Excel

Level 1 - To protect your excel files, there are a few steps required to make it more difficult for other users to by pass security. To prevent changes made to the worksheet, you need to protect your worksheet. See protecting sheets. To prevent sheets from being renamed, moved or deleted, protect the workbook. However protection of worksheets and workbook can easily be hacked using macros as shown by an Excel developer. I believe the next level of protection is protecting your macros. To protect your macros, point at your project in the explorer window, right click on it and select VBA project properties, click on the Protection tab, check on Lock Project for Viewing and next key in your password and you're done. Now the project cannot be viewed or amended.

Level 2 - The next step is to force the user to enable your macro when opening your file. The best way is to use a macro to hide the important sheets (see Hiding sheets) when saving your file. Upon opening the file, a macro will be used to unhide these sheets. If the user disables the macros when opening the worksheet, they will not be able to view your worksheet unless they allow the macro to run.

Level 3 - The final step is to put an expiry date for your worksheet or your macro. However this has a draw back as the user may change the system date of the computer to by pass the step. Alternatively you can use a counter (Refer Counter Macro) to allow a fixed number of access to your worksheet or macro. Here you need to save the counter value each time the file or macro is used. Upon reaching the defined limit, disable the macro or disable the access of your worksheet.

The steps mentioned above are not 100% fool proof. But it will keep normal users out but not hackers and crackers. Here I will not supply the code as this can be lengthy and may be difficult to understand but I believe these steps may be useful to some of you out there.

No comments:

Post a Comment