How to Protect Your Excel Application from Unlicensed Use

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

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 And A Password Of Your Choice

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

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.

Images On Welcome Worksheets

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.

Author Introduction:

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

 

Comments are closed.