Solved: Force use of macros/Enable trial period

[chrismdusa]

Hello all. While pretty experienced in Excel, for years I've managed to avoid the VBA aspect of it like it was the plague. I can't put it off any more...it's finally time to jump in...

I have a fairly large workbook that I'm trying to put 2 macros into. One is the "Force Users To Enable Macros" macro located at http://www.vbaexpress.com/kb/getarticle.php?kb_id=578 , and the other is the "Set a Trial Period For Use Of Your Project" located at http://vbaexpress.com/kb/getarticle.php?kb_id=475 .

I'm using Excel 2003 running on a Dell WinXP machine. I follow the instructions from the 'Enable Macros' macro, but when I get to instruction # 3 where it says to go to Tools/Project Explorer, I don't see 'Project Explorer' under the Tools menu. Can someone run me through the exact steps I should go through to get the code in the right place? Also, what's different when I want to put 2 macros in 1 workbook...do I put them in the same place, I guess that would be a 'Module', and run them together one after the other? Or does the 2nd one go into a 2nd module?

Thanks very much in advance for the help!

[XLGibbs]

The Project explorer is in the VBA code window. YOu can open that window by either pressing Alt & F11 keys or by going to Tools>Macro>Visual Basic Editor

The project explorer would be down the left hand side. The code would go in the appropriate file-object in the code windows . You can put both macros in a standard module. In your file (you will see the name of your file in the project explorer like VBAProject (Book1.xls) for example.....you would right click the project and Insert>Module. Click the module and paste the code into the window on the right..

[johnske]

My oops! Sorry, that should have been View/Project Explorer. I've just corrected it...

EDIT: Also, if you're putting the enable macros code into a pre-existing workbook you'll also need to insert a new worksheet and name it "Prompt"

[chrismdusa]

Thanks both of you....trying again now.

[chrismdusa]

Hi John. I put the Force Macros Enabled code in, set my macro security level to very high, put a new sheet in called "prompt", closed and opened the workbook. I get a notice from Excel that tells me the level is set to very high, and to lower it to use the macros...then the workbook finishes opening up.

So then, I decided to copy the "prompt" sheet from the example workbook into my spreadsheet to see what happens. Same thing.

What could I be doing wrong? Thanks.

[chrismdusa]

One more question...I have the "Set a Trial Period For Use Of Your Project" macro in my worksheet, and working fine. How do I get it to NOT save any data at the expiration point? At the expiration, I just want it to close. Thanks again.

[johnske]

One more question...I have the "Set a Trial Period For Use Of Your Project" macro in my worksheet, and working fine. How do I get it to NOT save any data at the expiration point? At the expiration, I just want it to close. Thanks again.

That could be done, but have a good think about it first and consider this...

You are providing code that's freely available to a user for a trial period and the user may well have put data in and processed it using your code and that could, or could not, have then been easily copied and saved elsewhere.

If they haven't copied it, this may very well be the only copy of that data the user has and the data itself belongs to them - all you own is the method(s) for processing the data - so you really have no right to delete their data without an immediate prior warning and an option to save it.

Not only that, if your intent is to (later) sell your full version to them they're going to be really P***** off and are not going to buy it after doing that to them - it's simply not good etiquette to destroy their data.

On the other hand, if the workbook is still intact on their PC and is simply inaccessable to them, they're much more likely to buy and get instructions on how to resuscitate it.

[johnske]

Hi John. I put the Force Macros Enabled code in, set my macro security level to very high, put a new sheet in called "prompt", closed and opened the workbook. I get a notice from Excel that tells me the level is set to very high, and to lower it to use the macros...then the workbook finishes opening up.

So then, I decided to copy the "prompt" sheet from the example workbook into my spreadsheet to see what happens. Same thing.

What could I be doing wrong? Thanks.

Not sure of the sequence of events that you followed here...

There's no need to copy the prompt sheet, the message on it is only a text box that can be copied and pasted to the new prompt sheet.

Put your security to high again (this means only the prompt sheet will be shown when your workbook and the example workbook are opened) open both these workbooks then copy and paste the prompt message from the example workbook to the prompt sheet in your workbook.

Now put your security to medium (or low - though not usually recommended) then save and close the workbook. Re-open the workbook and you should only see the prompt sheet for a fraction of a second, then it will disappear and all the other sheets should appear. I have no problems and no-one else has ever reported any problems with it so it should work fine...

Also, if you're using this in conjunction with the other code ("Set a Trial Period...") you need to note they both use workbook open events. You can't have two separate open events in the same workbook so you would have to rename the Workbook_Open procedure in the 'Set a Trial Period' code to something else and call (read your VBA Help files) this procedure at the end of the Workbook_Open procedure for the 'Force Macros' code.

[chrismdusa]

In case I had done something wrong when I copied the code in, I tried copying the prompt sheet from the sample workbook to my workbook. I wasn't sure if the macro would be attached to the sheet when copied. I guess it's not.

I started again from scratch and copied the code into my workbook. I think it's in right now. But I have an "Admin" sheet in my workbook that I want to stay hidden at all times. I usually use Format/Sheet/Hide, but I guess the macro supersedes that. How can I make the Admin sheet always stay hidden...unless I'm working on it.?

Then also, the workbook has 4 charts in it. When disabling macros, the charts are staying visible along with the Prompt sheet. How do I make the charts hide along with the other sheets?

[chrismdusa]

Also, I see your point about making the person not be able to access the data that they may have input once the time period has expired. The problem I have with saving the data is that it is also saving a few hundred hidden formulas for the user that I don't want copied.

How about this...once the allowed time period has expired, instead of the program not opening, how about if the macro puts "expired" into cell F64 of the "Welcome" sheet (the first sheet at the far left)? Is that possible?

[chrismdusa]

...ok don't know why those previous posts were entered while I was still typing. Hmmmmm.

Anyway, what I was typing was....

Sorry, 1 more thing. When I enable macros, I get a "Run-Time error '1004': Method Goto of object '_Application' failed

Then when I debug, Application.Goto Sheet1.[A1], scroll:=True is highlighted in the code. Any ideas why?

Thanks again.

[chrismdusa]

Don't know if I was going the right way on this, but I just tried changing "sheet1" in the debug highlighted code to "Welcome" since that's the name of my first sheet. When I did that I got the same error, and when I debugged it highlighted the next line 'Set Sheet = Nothing'. It looks like you're about 15 hours ahead of me. I might have a bit of a wait, but I'll watch for your reply. Thanks.

[johnske]

Also, I see your point about making the person not be able to access the data that they may have input once the time period has expired. The problem I have with saving the data is that it is also saving a few hundred hidden formulas for the user that I don't want copied.

How about this...once the allowed time period has expired, instead of the program not opening, how about if the macro puts "expired" into cell F64 of the "Welcome" sheet (the first sheet at the far left)? Is that possible?

Did you read this? [vba] With .ActiveSheet
.Paste
'//N.B. to remove all the cell formulas,
'//uncomment the 4 lines of code below...
'With Cells
'.Copy
'.PasteSpecial Paste:=xlPasteValues
'End With
.Name = SheetName
[A1].Select
End With [/vba]Uncomment means remove the leading apostrophes.

If you read the declarations up the top you'll see that Sheet was declared to be a Worksheet (the usual case) when you venture into the unusual you have to modify the code to suit.

The GoTo error can only be because you've made Sheet1 the hidden (Prompt) sheet. Read Referencing worksheets.

Replace your ThisWorkbook code with this modified code...
[vba]
Option Explicit
'
Private Sub Workbook_Open()
'
Dim Sheet As Worksheet, Cht As Chart
'
'make all sheets visible
For Each Sheet In Worksheets
If Sheet.Name <> "Prompt" Then
Sheet.Visible = xlSheetVisible
End If
Next Sheet
'
'make all charts visible
For Each Cht In Charts
Cht.Visible = xlSheetVisible
Next Cht
'
'hide the prompt and go to A1 on sheet(1)
Sheets("Prompt").Visible = xlSheetVeryHidden
Application.Goto Sheets(1).[A1], scroll:=True
'
'clean up
Set Sheet = Nothing
ActiveWorkbook.Saved = True
End Sub
'
'
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Sheet As Worksheet, Cht As Chart
'
With Sheets("Prompt")
'if book is already saved, make a note of it
If ActiveWorkbook.Saved = True Then .[A100] = "Saved"
'make prompt sheet visible
.Visible = xlSheetVisible
'hide all other sheets
'
For Each Sheet In Worksheets
If Sheet.Name <> "Prompt" Then
Sheet.Visible = xlSheetVeryHidden
End If
Next Sheet
'
For Each Cht In Charts
Cht.Visible = xlSheetVeryHidden
Next Cht
'
'if the book is already saved, delete
'the previous note and close the book
If .[A100] = "Saved" Then
.[A100].ClearContents
ActiveWorkbook.Save
End If
'
'clean up
Set Sheet = Nothing
End With
End Sub
[/vba]Am busy this morning, will get back to you later about the other question

[chrismdusa]

Hi again John. I just sent you a private message.

[johnske]

This issue has been resolved via email...

For those following the thread and curious as to exactly how to amalgamate the two KB entries "Force Macro Use" and "Set a Trial Period For Use of Your Project", (perhaps with similar issues) here's a variation of the code given to chrismdusa by email
[vba]Option Explicit
'
Private Sub Workbook_Open()
'
'<< CHECK FIRST IF TRIAL PERIOD HAS/HASN'T EXPIRED >>
'
'disable the ESC (escape) key first :o)
With Application
.EnableCancelKey = xlDisabled
'
'now declare variables and constants
Dim StartTime As Double, CurrentTime As Double
Dim ObscurePath As String
'
'*****************************************
'SET YOUR OWN TRIAL PERIOD
'Integers (1, 2, 3,...etc) = number of days use
'1/24 = 1Hr, 1/48 = 30Mins, 1/144 = 10Mins use
Const TrialPeriod As Double = 5 '< 5 days trial
'
'SET YOUR OWN OBSCURE FILE_NAME
Const ObscureFile As String = "TestFile.log"
'
'SET YOUR OWN OBSCURE PATH BELOW
'N.B. Left(ActiveWorkbook.Path, 2) returns the local drive path
'(some ppl use drives other than "C:" {or may rename them})
ObscurePath = Left(ActiveWorkbook.Path, 2) & "\"
'*****************************************
'
.ScreenUpdating = False
'
'<< IF THIS IS THE 1ST TIME THE BOOK WAS OPENED >>
If Dir(ObscurePath & ObscureFile) = Empty Then
'
StartTime = Format(Now, "#0.#########0")
'
'create a new log file
Open ObscurePath & ObscureFile For Output As #1
Print #1, StartTime
Close #1
'
Call ShowSheets
'
Else
'
'<< ELSE THIS'S NOT THE 1ST TIME THE BOOK WAS OPENED >>
Open ObscurePath & ObscureFile For Input As #1
Input #1, StartTime
CurrentTime = Format(Now, "#0.#########0")
'
'<< IF THE TRIAL PERIOD HASN'T EXPIRED, SHOW SHEETS >>
If CurrentTime < StartTime + TrialPeriod Then
Close #1
Call ShowSheets
.EnableCancelKey = xlInterrupt
Exit Sub
'
Else
'
'<< ELSE THE TRIAL PERIOD HAS EXPIRED >>
If Sheets("Prompt").[A1] <> "Expired" Then
'
'let user know their trial period has expired
MsgBox "Sorry, your trial period has expired - your data" & vbLf & _
"will now be extracted and saved for you..." & vbLf & _
"" & vbLf & _
"This workbook will then be made unusable."
Close #1
'
'save their data
Call SaveShtsAsBook
'
'flag it's expired, save and quit
Sheets("Prompt").[A1] = "Expired"
ThisWorkbook.Save
.DisplayAlerts = False
.Quit
'
Else
'
'it's already been marked as expired so just quit
Close #1
ThisWorkbook.Saved = True
.DisplayAlerts = False
.Quit
'
End If
End If
End If
'
're-enable the ESC (escape) key
.EnableCancelKey = xlInterrupt
.DisplayAlerts = True
.ScreenUpdating = True
End With
'
End Sub
'
'
Private Sub ShowSheets()
'
'<< MACROS ARE ENABLED & STILL WITHIN TRIAL PERIOD >>
'
Dim N As Long
'
With Application
.ScreenUpdating = False
'
'make all sheets visible
For N = 1 To Sheets.Count
Sheets(N).Visible = xlSheetVisible
Next
'
'hide the prompt and admin sheets
Sheets("Prompt").Visible = xlSheetVeryHidden
'
On Error Resume Next
Sheets("Admin").Visible = xlSheetVeryHidden
On Error Goto 0
'
'go to A1 on sheet(1)
.Goto Sheets(1).[A1], scroll:=True
'
ActiveWorkbook.Saved = True
.ScreenUpdating = True
End With
'
End Sub
'
'
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
'<< HIDE SHEETS TO FORCE MACROS USE WHEN OPENED >>
'
Dim N As Long
'
With Application
.ScreenUpdating = False
'
With Sheets("Prompt")
'
'if book is already saved, make a note of it
If ActiveWorkbook.Saved = True Then .[A100] = "Saved"
'
'make prompt sheet visible
.Visible = xlSheetVisible
'
'hide all other sheets
For N = 1 To Sheets.Count
If Sheets(N).Name <> "Prompt" Then
Sheets(N).Visible = xlSheetVeryHidden
End If
Next
'
'if the book is already saved, delete
'the previous note and close the book
If .[A100] = "Saved" Then
.[A100].ClearContents
ActiveWorkbook.Save
End If
'
End With
'
.ScreenUpdating = True
End With
'
End Sub
'
'
Private Sub SaveShtsAsBook()
'
'<< TRIAL PERIOD IS EXPIRED, SAVE THE USERS DATA >>
'
Dim SheetName As String, MyFilePath As String
Dim N As Long
'
MyFilePath$ = ActiveWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
'
With Application
.ScreenUpdating = False
.DisplayAlerts = False
'
On Error Resume Next '<< a folder exists
MkDir MyFilePath '<< create a folder
'
Call ShowSheets
'
For N = 1 To Sheets.Count
'
Sheets(N).Activate
'
Select Case ActiveSheet.Name
'
Case "Prompt", "Admin", "Chart1", "Chart2", "Chart3", "Chart4"
'don't copy these sheets
'
Case Else 'copy the sheet
'
SheetName = ActiveSheet.Name
Cells.Copy
'
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
'
With .ActiveSheet
.Paste
'//N.B. to keep all the cell formulas,
'//delete the 4 lines of code below...
'
With Cells
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
'
.Name = SheetName
[A1].Select
End With
'
'save book in this folder
.SaveAs Filename:=MyFilePath _
& "\" & SheetName & ".xls"
.Close SaveChanges:=True
'
End With
.CutCopyMode = False
'
End Select
Next
'
End With
'
'generate a text message to put in the same folder
Open MyFilePath & "\READ ME.log" For Output As #1
Print #1, "Sorry, your trial period has expired."
Print #1, "The program will now be made unusable."
Print #1, "To purchase an unrestricted copy,"
Print #1, "email xxxx@yyyy.com"
Close #1
'
End Sub[/vba]