A developer who intends to license an application might wish to limit its use to, say, a three day trial before locking out unregistered users. One way to do this is discussed below.
The article assumes the reader has the Developer ribbon displayed and is familiar with the VBA Editor. If not, please Google “Excel Developer Tab” or “Excel Code Window”.
The xlsm in this exercise can be downloaded here. When it opens, it might request a password, which is 123. The development code discussed below is still present; click the logo to unhide all sheets.
The “Main” Sheet
Our application has three sheets, one of which, “Main”, is used to drive the application. Sheet “Main” will not open to unauthorized users:
Add two buttons and a logo to “Main” sheet
The buttons have no function in this example, but merely mimic entry to the application. Unauthorised users will not get this far
The “Defaults” sheet
The sheet (“Defaults”) holds Expiry Date details:
Enter an ExpiryDate set in the future, and a password of your choice
The “Welcome” sheet
A third tab exists in this example because Excel requires at least one visible sheet, and we have hidden the others.
Create a “Welcome” sheet with an image or object that can be clicked
This sheet has no other functionality in this example, though we will place some temporary code behind the logo image to help us with testing.
Once expiry is announced, users might hope to circumvent lockouts by backdating their computer date/time. We prevent this by using a “lock flag” in our defaults that, once set to “Yes”, will require a password regardless of computer date.
The Complete VBA Code
“Auto_open” is a procedure that automatically runs when a workbook opens. It is here that we will place code to unhide sheet “Main” if the user is legitimate. In the same way, “Auto_close” will hide necessary sheets when the workbook is closed.
Names of modules and variables are a matter of personal preference.
Insert a module, and enter the following:
Option Explicit Dim dteExpiry As Date ‘Declare variables Dim flagLocked As String Dim strPWD As String Dim strResult As String Sub Auto_open() Sheets("Welcome").Visible = True Sheets("Main").Visible = xlVeryHidden 'Can't be unhidden by the user Sheets("Defaults").Visible = xlVeryHidden dteExpiry = Sheets("Defaults").Range("B1") 'Test for Expiry strPWD = Sheets("Defaults").Cells(2, 2) flagLocked = Sheets("Defaults").Cells(3, 2) If dteExpiry < Now() And flagLocked = "No" Then 'Trial period over flagLocked = "Yes" Sheets("Defaults").Cells(3, 2) = flagLocked ActiveWorkbook.Save 'Ensures that flagLocked is set MsgBox "The trial period has expired. Please contact xxxx at yyyyy" ElseIf flagLocked = "Yes" Then strResult = InputBox("Enter password", "Password", vbOKCancel) If strResult = strPWD Then Sheets("Main").Visible = True 'Reveal the "Main" sheet Sheets("Welcome").Visible = False Else MsgBox "The password is wrong. Please contact xxxx at yyyyy" Exit Sub End If Else ‘Within trial period Sheets("Main").Visible = True Sheets("Main").Activate Sheets("Welcome").Visible = False End If End Sub Sub Auto_Close() Sheets("Welcome").Visible = True Sheets("Main").Visible = xlVeryHidden Sheets("Defaults").Visible = xlVeryHidden ActiveWorkbook.Save End Sub
It is tedious during development and testing to keep un-hiding sheets or resetting defaults. Placing temporary code that is activated by clicking on a logo image, for example, can do the work. Remember to delete this code before releasing the application.
Temporary code behind logo:
Sub Development() ‘Initialise the sheets Sheets("Welcome").Visible = True Sheets("Main").Visible = True Sheets("Defaults").Visible = True Sheets("Defaults").Cells(3, 2) = "No" Sheets("Defaults").Activate End Sub
Right click the images on both “Welcome” and “Main” worksheets to assign this macro to them.
You can also protect your code from being viewed or changed by going to: Tools>Properties>Protection.
Save and close the workbook as a type xlsm.
Open the workbook to test the code.
More Security Measures
Most security barriers can be broken down if a hacker tries hard enough. Our object is to make it tiresome to do so. Selectively hiding Buttons in the “Main” sheet, or restricting menus are more safety measures. Such measures are not covered here however, and it’s unlikely you will need to go to such lengths.
Deal with Excel File Corruption
With our daily usage of Excel and developing applications for it, we may encounter Excel file corruptions from time to time. This is a disaster for most Excel users, including the experts. However, if you have an effective Excel recovery tool in hand, then you can solve such an issue easily and quickly.
Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair rar file and sql recovery software products. For more information visit www.datanumen.com