Hi
I have a workbook , i would like to give a Trial Period of 7 days ,
after that they should register inorder for them to continue using the
work book

can anyone provide macro code to do the same

i have no knowledge on vba coding , if you could explain how to do this
, i m greatful to y9ou

thank you in advance

Regards
Jay

"Jay" schreef in bericht
oups.com...
Hi
I have a workbook , i would like to give a Trial Period of 7 days ,
after that they should register inorder for them to continue using the
work book

can anyone provide macro code to do the same

i have no knowledge on vba coding , if you could explain how to do this
, i m greatful to y9ou

thank you in advance

Regards
Jay




Hi Jay,

U can use the Windows registry. Write the date first used, and check every
time the workbook is opened if the install date is less than 7 days ago or
more...

Private Sub Auto_Open()
Dim DateInst As Date
Dim DateUsed As Date
Dim wsh
Dim RegKey As String
Dim RegVal As String
Set wsh = CreateObject("WScript.Shell")
RegVal = "HKLM\SOFTWARE\YourName\InstallDate"
On Error Resume Next
RegKey = wsh.RegRead(RegVal)
If RegKey = Empty Then
DateInst = Format(Now(), "mm-dd-yyyy")
wsh.RegWrite RegVal, DateInst, "REG_SZ"
Else
If DateValue(Format(Now(), "mm-dd-yyyy")) - DateValue(RegKey) 7
Then
MsgBox "Expired! Please contact blah blah blah"
Application.DisplayAlerts = False
ThisWorkbook.Close
End If
End If
Set wsh = Nothing
End Sub


I used a simple sample, but you'd better use a sneaky registry key, just in
case of a smart user. Also protect the VBA project with a steady password.
Play around with the code above which you should put in a Module in Visual
Basic Editor, try to find out how it works and check your registry after
running, and if it's too tough, just ask.

Greetings,

A VBA approach such as Moon's will definitely work but has the
potential drawback that it might be circumventable by the expedient of
disabling macros. If your spreadsheet needs macros to operate then this
wouldn't be an option for the user, but if the only VBA code is to
implement a trial period then it might be a problem.

It is possible to sabotage a spreadsheet so that it stops working
properly in 7 days, even if macros are disabled. The idea is to replace
*each* formula, F, on the spreadsheet by say

=If(Today() < Date(2006,10,17),F,"Please Register").

This can be done in a macro (to be run before distributing the
spreadsheet, and assuming that the sheets are not yet protected):
_____________________________________

Sub TimeTrial()
Dim ws As Worksheet
Dim cl As Range
Dim expDate As Date
Dim expDateString As String

expDate = DateAdd("d", 7, Date)
expDateString = "Date(" & Year(expDate) & "," & Month(expDate) & "," &
_
Day(expDate) & ")"
For Each ws In ActiveWorkbook.Sheets
For Each cl In ws.UsedRange.Cells
If cl.HasFormula = True Then
cl.Formula = "=If(Today()<" & _
expDateString & "," & Mid(cl.Formula, 2) & _
", ""Please Register"")"
End If
Next cl
Next ws

End Sub
__________________________________________________ _________________

After running this sub, password-protect the sheet so that these
formulas are both hidden and protected. Everything will be functionally
identical *until* the 7 days expires - then all of the formulas turn
into an annoying reminder to register.

If you want to go this route, a matching macro would have to be written
to restore the formulas to their original condition. This would be a
somewhat annoying parsing problem and would involve monkeying around
with worksheet protection- so I won't pursue it anymore unless there is
interest.

Just an idea

-John Coleman


Jay wrote:
Hi
I have a workbook , i would like to give a Trial Period of 7 days ,
after that they should register inorder for them to continue using the
work book

can anyone provide macro code to do the same

i have no knowledge on vba coding , if you could explain how to do this
, i m greatful to y9ou

thank you in advance

Regards
Jay

"John Coleman" schreef in bericht
oups.com...
Greetings,

A VBA approach such as Moon's will definitely work but has the
potential drawback that it might be circumventable by the expedient of
disabling macros.


Be inventive, let the workbook come up with an empty sheet by default and
show the real stuff if macro's is enabled.

moon wrote:
"John Coleman" schreef in bericht
oups.com...
Greetings,

A VBA approach such as Moon's will definitely work but has the
potential drawback that it might be circumventable by the expedient of
disabling macros.


Be inventive, let the workbook come up with an empty sheet by default and
show the real stuff if macro's is enabled.

Pretty good idea. At first I thought that you could defeat it by
disabling macros *after* the workbook is open (so that the before_close
or auto_close macros which presumably rehide the sheets wouldn't fire)
but discovered that disabling macros while a workbook is open only
takes effect after the workbook is closed. Then I thought of typing
"Application.EnableEvents = False" in the immediate window. That *will*
disable the before_close event but not the auto_close macro. Finally, I
wrote a script:

'MakeVisible.vbs

Option Explicit

Dim xlApp, wb, ws

Set xlApp = CreateObject("Excel.Application")
xlApp.EnableEvents = False
Set wb = xlapp.Workbooks.Open("trial.xls")

For Each ws in wb.Worksheets
ws.Visible = -1 'xlSheetVisible
Next
wb.Save
wb.Close
Set wb = Nothing
Set xlapp = Nothing

This seems to make all sheets visible without running any macros, but
maybe I'm missing something.

A non-programmer wouln't have the requisite know-how, but it caught my
interest when in your earlier post (which, by the way, contained some
nice code) you warned about sophisticated users who might know how to
edit registry keys. I wondered how such users could thwart the VBA and
how the VBA programmer could in turn thwart them.

An idea I had in an earlier thread (concerning copy-protection) is to
make VBA functionally essential (if it isn't already) by replacing
spreadsheet formulas by user-defined functions which duplicate their
functionality. In many cases this could be achieved by dummy functions
which call the corresponding Application.WorksheetFunction or use
Evaluate on the string representing the original function (with
appropriate substitutions). This would entail some programming overhead
- but you only have to do it on a handful of crucial formulas to
disable the spreadsheet (there better be some pretty special formulas
to justify charging money for the file). The user-defined functions
might entail some overhead, so the macro to be run upon registration
could replace them by the corresponding worksheet functions.

It all depends on how paranoid you want (or need) to be.

-John Coleman

Cool piece of text, the VBS is quite okay.
But indeed, how far can one go? Even distributing a (read only) file on
CD-Rom won't work because we both know that just everything is hackable; if
you secured a workbook the ultimate way, then there's always a hex-editor
which is so generous to show us the project password in plain text.
Do you remember you ever failed when trying to crack a 'protected' workbook?
I guess not.
So help from an external application (like a dll) is definitely required
when it comes on protecting MS Office user files.

Hans




"John Coleman" schreef in bericht
oups.com...

moon wrote:
"John Coleman" schreef in bericht
oups.com...
Greetings,

A VBA approach such as Moon's will definitely work but has the
potential drawback that it might be circumventable by the expedient of
disabling macros.


Be inventive, let the workbook come up with an empty sheet by default and
show the real stuff if macro's is enabled.

Pretty good idea. At first I thought that you could defeat it by
disabling macros *after* the workbook is open (so that the before_close
or auto_close macros which presumably rehide the sheets wouldn't fire)
but discovered that disabling macros while a workbook is open only
takes effect after the workbook is closed. Then I thought of typing
"Application.EnableEvents = False" in the immediate window. That *will*
disable the before_close event but not the auto_close macro. Finally, I
wrote a script:

'MakeVisible.vbs

Option Explicit

Dim xlApp, wb, ws

Set xlApp = CreateObject("Excel.Application")
xlApp.EnableEvents = False
Set wb = xlapp.Workbooks.Open("trial.xls")

For Each ws in wb.Worksheets
ws.Visible = -1 'xlSheetVisible
Next
wb.Save
wb.Close
Set wb = Nothing
Set xlapp = Nothing

This seems to make all sheets visible without running any macros, but
maybe I'm missing something.

A non-programmer wouln't have the requisite know-how, but it caught my
interest when in your earlier post (which, by the way, contained some
nice code) you warned about sophisticated users who might know how to
edit registry keys. I wondered how such users could thwart the VBA and
how the VBA programmer could in turn thwart them.

An idea I had in an earlier thread (concerning copy-protection) is to
make VBA functionally essential (if it isn't already) by replacing
spreadsheet formulas by user-defined functions which duplicate their
functionality. In many cases this could be achieved by dummy functions
which call the corresponding Application.WorksheetFunction or use
Evaluate on the string representing the original function (with
appropriate substitutions). This would entail some programming overhead
- but you only have to do it on a handful of crucial formulas to
disable the spreadsheet (there better be some pretty special formulas
to justify charging money for the file). The user-defined functions
might entail some overhead, so the macro to be run upon registration
could replace them by the corresponding worksheet functions.

It all depends on how paranoid you want (or need) to be.

-John Coleman

thanx every one for your input ,

sorry i couldnt reply as i was out of station for few days.

Can you please tell me where shall i put this code on ? please help

if you can give me step by step procedures to add this code
that would be great

thanx again
Regards
Jay
moon wrote:
Cool piece of text, the VBS is quite okay.
But indeed, how far can one go? Even distributing a (read only) file on
CD-Rom won't work because we both know that just everything is hackable; if
you secured a workbook the ultimate way, then there's always a hex-editor
which is so generous to show us the project password in plain text.
Do you remember you ever failed when trying to crack a 'protected' workbook?
I guess not.
So help from an external application (like a dll) is definitely required
when it comes on protecting MS Office user files.

Hans




"John Coleman" schreef in bericht
oups.com...

moon wrote:
"John Coleman" schreef in bericht
oups.com...
Greetings,

A VBA approach such as Moon's will definitely work but has the
potential drawback that it might be circumventable by the expedient of
disabling macros.


Be inventive, let the workbook come up with an empty sheet by default and
show the real stuff if macro's is enabled.

Pretty good idea. At first I thought that you could defeat it by
disabling macros *after* the workbook is open (so that the before_close
or auto_close macros which presumably rehide the sheets wouldn't fire)
but discovered that disabling macros while a workbook is open only
takes effect after the workbook is closed. Then I thought of typing
"Application.EnableEvents = False" in the immediate window. That *will*
disable the before_close event but not the auto_close macro. Finally, I
wrote a script:

'MakeVisible.vbs

Option Explicit

Dim xlApp, wb, ws

Set xlApp = CreateObject("Excel.Application")
xlApp.EnableEvents = False
Set wb = xlapp.Workbooks.Open("trial.xls")

For Each ws in wb.Worksheets
ws.Visible = -1 'xlSheetVisible
Next
wb.Save
wb.Close
Set wb = Nothing
Set xlapp = Nothing

This seems to make all sheets visible without running any macros, but
maybe I'm missing something.

A non-programmer wouln't have the requisite know-how, but it caught my
interest when in your earlier post (which, by the way, contained some
nice code) you warned about sophisticated users who might know how to
edit registry keys. I wondered how such users could thwart the VBA and
how the VBA programmer could in turn thwart them.

An idea I had in an earlier thread (concerning copy-protection) is to
make VBA functionally essential (if it isn't already) by replacing
spreadsheet formulas by user-defined functions which duplicate their
functionality. In many cases this could be achieved by dummy functions
which call the corresponding Application.WorksheetFunction or use
Evaluate on the string representing the original function (with
appropriate substitutions). This would entail some programming overhead
- but you only have to do it on a handful of crucial formulas to
disable the spreadsheet (there better be some pretty special formulas
to justify charging money for the file). The user-defined functions
might entail some overhead, so the macro to be run upon registration
could replace them by the corresponding worksheet functions.

It all depends on how paranoid you want (or need) to be.

-John Coleman