To start, Everyone will tell you that Excel is not secure...and they're right. If someone wants in, they'll get in. My school of thought however, is if we can prevent the majority of users from doing that, it's a win. I've put together a few workbooks that use various methods of thwarting those types of users that include checks to ensure they can only open a workbook from a workstation on a specific domain; only specific users have access; only domain user has access; only certain users can saveas; login audit log; "black holes" which destroy the content and send me details of who bypassed security measures and from which workstation; password to open; etc...I've even tried a timed "subscription" based approach. Most of those methods work quite well for the most part.
One of the challenges becomes shared workbooks...where users have access to different sheets. Depending on how sharing is configured in your organization, if two people are both logged into the sheet they may both see each others work etc...but that's a whole other topic.
Back to the "security" aspect...
Since VBA doesn't work on Excel for Web, we need to put in some effort to make sure the user is opening the workbook with a desktop application. If opened in the web app, I typically use conditional formatting to "blank out" all content on a visible sheet except for a message to the user stating that they must open the workbook in the desktop application; as well as a caveat that says something along the line of any attempts to circumvent security are prohibited and may result in loss of data.
My approach typically starts with hiding (veryhidden) all sheets except one on both a workbook_open and a workbook_beforeclose event. This will force the user(s) to 'enable macros' in order to get access to any meaningful parts of the application. Now that macros are enabled we need to decide what measures we want to take to keep this secure as possible. I typically opt for the following at a minimum:
- Password to open (encrypted)
- Workbook can only be opened from specific domain connected device and confirms the user is in active directory
- Admin login field on main sheet which, if the correct password was entered, will display workbook management tab with details such as current vba project password, admin password, etc.
- Only user(s) identified as admin of the workbook can do a 'saveas'
- All hidden sheets are 'veryhidden' vs just 'hidden'
- Password protected vba project
- User Audit Log
- "black hole"
- To note with this approach. Since the sheet gets destroyed...in case the "hacker" is doing it on the main copy of the workbook you'll want to ensure you have a good backup strategy in your organization; and/or your own backup strategy for this workbook, for example a batch script that runs on your workstation that makes a copy of the workbook at regular intervals.
You mentioned a possible licensing system...I would include all of the above features, in addition to when the workbook first opens you can have it display the current expiry date and have a field where the user would enter a code to extend the expiry date. This license code could be programmed to use whatever algorithm you choose. It can be fairly simple, or complex. I typically create a "subscription generator" workbook that I use to build license codes that I would give to the users.
