How to Secure An Excel Application

                      AutoSoft Systems
        AutoSoft Systems 2 Round Hill Court
        East Greenwich, RI 02818
        401.884.5653 Fax
                        401.996.3631 Cell
AMDG       Decision Ready Information!
Commercial & custom multi-user computer software for a variety of applications including performance metrics, statistical analysis, data extraction and merger from multiple large databases, computer simulation and management information systems.  Founded in 1982.
We know Excel! Whenever possible, Excel is the user interface, VBA is the programming language, ODBC connects to the database of your choice.
Home About Us Literature Wire & Cable Pharmaceutical & Biotech Construction Management Commercial Products Custom Solutions
Contact Us Events Downloads
How To Secure An Excel Application
Excel is an incredibly flexible and powerful tool.  There are some legitamite concerns about securing and validating Excel documents which can be addressed simply and quickly.  Here are features I have employed in the past to do so:
Password Protection - one can protect an Excel document at 3 different levels:
    - Workbook level protection
    - Worksheet level protection
    - Visual Basic Code protection
    As a further safeguard, once an Excel application has been fully validated or verified, one can make a copy of the workbook, then create a random password, protect the copy with the random password, then discard the password.  By doing this, the document is fully protected, and this working copy, which is then distributed, cannot be unprotected for unauthorized modifications because NOBODY knows the password.  Of course, the master copy, which is not password protected, or protected with a password known to authorized individuals, is kept in a secure location.
  Remove Default Commands from the Excel Ribbon
    A programmer has the ability to edit the XML code of an Excel workbook and strip off the default Excel ribbons, replacing it with a Ribbon that only has the desired commands.  Here are two examples of such a practice:
- Project Portfolio Manager
      - MATRIX - Database of Databases            
  Lock or Hide cells that contain equations, only allowing users to edit certain cells
One has the ability to include in the Cell's formatting properties a LOCK, which restricts users from editing or optionally even selecting a locked cell.
  Hide some of the worksheets in a workbook:
One has the ability to hide worksheets at two different levels (Hidden and Very Hidden).  Here is the screen shot from the VBA Shell indicating the 3 options for a worksheet.
                  Is the "Very Hidden" option sort of like "Double Secret Probation"?      
  Use Data Validation to assure the user only enters valid choices on text entry fields.
    Illustration of a Drop Down Box in a cell:
    Illustration of contraints that can be placed on a cell:  
    The Data Validation Control:        
Use a VBA Form with a text box that has as Masked Password
Data Encryption
I have many ways of encrypting data at all levels.  Here is a screen shot of a simple password encryptor:
  In this example, the password "Welcome@123" is encrypted in the long string of numbers.                    
Payroll is a particularly sensitive topic at many companies.  This screen shot, from a back end Access database used by a multi-user application, illustrates how Payroll information is encrypted at the field level inside the Payroll table of the database, so that even if a nefarious person obtained a copy of the Access database, confidential information is not compromised.
Validated Systems
Some applications require that software be validated.  General principals of software validation can be found at:
Some Validation "Rules of Thumb" that can be simply applied to any Excel workbook are quite simple:
  - Lock all cells except those cells where data entry is required.
  - Use data validation on all data entry cells.
  - After the file has been checked, doubled check and you are assured it does exactly what is desired:
  - make a copy of the file
    - password protect that copy at the workbook, worksheets and VBA code level USING A RANDOM PASSWORD
    - Throw away the password (remember to always save a copy of the file with no password for future work).
  - Only use the copy of the file with the unknown password
  - Take on any auditor with supreme confidence.
AutoSoft Systems | 2 Round Hill Court, East Greenwich, Rhode Island, USA 02818 | 401.885.3631 | Fax: 401.884.5653 | Mobile: 401.996.3631 
This web page was last updated at 10/16/2021 09:40 AM and is written in EXCEL!
AXIOM is a Trademark of Consona - USYS is a Trademark of Zumbach - AutoSoft Systems is not affiliated with Consona nor Zumbach