MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Protecting an Excel Document using a Macro

Posted by Msi on June 22, 2001 2:47 PM

Dear Mr. Excel,

I want to offer a Project Finance Spreadsheet (MS Excel 2000) to a group of Project Financiers for a LIMITED period only. If a person is interested in the Financial Model, then he/she has to subscribe to my website (still being developed) for a fee, otherwise the Spreadsheet locks itself after the expiry date (e.g. 3 Months).

Is there a MACRO (or any other way) one can write to render the Spreadsheet useless after the offer period has expired?

I hope you will be able to help me.


Msi, Johannesburg, South Africa

Posted by Joe Was on June 23, 2001 8:29 AM

If a user is using your application and have inputted their data into it, you cannot lock their data! They may be using your application without paying for it, but if you lock your application, with their data, you will be stealing their data!

You can warn the user, to register their copy of your application, with code! To do this:

Build a macro called myMsg it should look something like this:

Sub myMsg()
'This is a called code.
'It displays my registration message.

Msgbox "If you like this application? " & vbLf & "Please; Register your copy! " & vbLf & "Phone: (xxx) xxx-xxx Now, To Register! "
End Sub

Then from the main worksheet in your application, Right click the "sheet tab" and select "View code."

Copy this code on to the code page:

Private Sub Workbook_Open()
Application.OnTimeNow + TimeValue("00:10:00"),"myMsg"
End Sub

Now, when the application is open for ten minutes, the sheet tab code will call the "myMsg" macro and send your warning message box to the screen. This will happen each time the application is used!

You can use the:


to reset this code, to reactivate, in a loop as long as the application is oppen, by adding the "OnTime" code and setting it with the reset time, at the end of the "Sheet Tab" code above. JSW

Posted by Dax on June 23, 2001 9:21 AM

Legal issues aside, you can use code that will work out how long it's been since the user first opened a workbook and then close if it's more than 90 days. In order for the macro to work effectively you'll have to have a single sheet (in this case I've called it Macros Disabled) which will be the only sheet visible if the user disables macros.

To use the code below, press Alt+F11 to open the VB Editor, click View, Project Explorer if you can't already see it, and then double click the ThisWorkbook icon. Then paste this code which will activate when the workbook is opened and closed.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Object
Application.ScreenUpdating = False
Sheets("Macros Disabled").Visible = xlSheetVisible

For Each sht In ThisWorkbook.Sheets
If sht.Name <> "Macros Disabled" Then sht.Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Open()
Dim sDateFirstUsed As String, sTodaydate As Long
Dim lConvert As Long, sht As Object

sDateFirstUsed = VBA.GetSetting("PED", "App", "Keyname", "NONE")

If sDateFirstUsed = "NONE" Then
'Write the date the user first opened the workbook
'as an encrypted number.
sTodaydate = Date Xor 123456
VBA.SaveSetting "PED", "App", "Keyname", sTodaydate
lConvert = CLng(sDateFirstUsed) Xor 123456
If lConvert > (CLng(Date) + 90) Then
MsgBox "You have been using this application for" & vbCrLf & _
"more than 90 days.", vbOKOnly, "Trial period exceeded"
ThisWorkbook.Close False
End If
End If
Application.ScreenUpdating = False
For Each sht In ThisWorkbook.Sheets
sht.Visible = xlSheetVisible
Sheets("Macros Disabled").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub

If you don't understand anything here then I'll happily explain.