Send a macro by email to change date in a spreadsheet?

[ikjohnson]

I have some scattered users who are trialling a spreadsheet. It contains code in the Private Sub Workbook_Open () that will check the system date against an expiry date I've set. If the system date is later than the expiry date, the spreadsheet cannot be opened.

I'd now like to extend the expiry date for these people.
Is there a way to create a macro that changes the expiry date to a later date?
Can I email something to them so that their spreadsheet is updated for the extended trial period?

 

[erik.van.geit]

ikjohnson,

I've made such a spreadsheet.
The expiredate is the result of a formula: release + period = expiredate

There is a button which first asks for a code and then adds more time to the period.
something like
If Inputbox... = "password" then Range("period") = Range("period") + 30

Can this fix your problem?

kind regards,
Erik

 

[Smitty]

I have some scattered users who are trialling a spreadsheet. It contains code in the Private Sub Workbook_Open () that will check the system date against an expiry date I've set. If the system date is later than the expiry date, the spreadsheet cannot be opened.

What's to keep them from just setting their system date back?

If the date is hardcoded in the code, then see Chip Pearsons's section on Programming to the VBE.

You can use a wb open event to update the code, then close the wb.

HTH,

Smitty

 

[erik.van.geit]

What's to keep them from just setting their system date back?

It's never secure for 100% but you can store the last date the workbook was opened (and closed )and then never allow a previous date.
You can also put a counter in it, etc...

kind regards,
Erik

 

[ikjohnson]

Thanks for the suggestions.

Erik replied:
"There is a button which first asks for a code and then adds more time to the period.
something like
If Inputbox... = "password" then Range("period") = Range("period") + 30"

Yes, that is the principle I had in mind, but Im not sure how to do it step by step. Could you provide some more detail?

Smitty replied:
"What's to keep them from just setting their system date back?"

Good point. I thought it possible some people might figure that out, so I've also included code to check for the system date being set back. If that occurs, a message box comes up advising the user, and clicking the OK button exits the workbook.

Thanks for the link to Chip Pearson's site. You also mentioned wb open events. Could you elaborate on this please, as it's not something I'm familiar with.

 

[Smitty]

You also mentioned wb open events. Could you elaborate on this please, as it's not something I'm familiar with.

Private Sub Workbook_Open is what I was referring to. You can use some of Chip's methods to open the workbook in question, update the code, then close both workbooks. This way you can send out an update workbook. I do it all the time and it works pretty well. Note that it's about as foolproof as relying on system time as it can be defeated by not enabling macros.

Smitty

 

[erik.van.geit]

If Inputbox... = "password" then Range("period") = Range("period") + 30"

Yes, that is the principle I had in mind, but Im not sure how to do it step by step. Could you provide some more detail?

Just name three ranges release, period, expiredate

expiredate is the sum of the others
increasing the period will set the expiredate to later
If InputBox("Please enter password", "PASSWORD", "") = "password" Then Range("period") = "50"
My example in previous post - just adding time to the period - needs another check to exclude adding time more then once. Therefore you could uwe another cell called "period_added" and set it to True...

Sub add_period()
If Range("period_added") = False Then
  If InputBox("Please enter password", "PASSWORD", "") = "password" Then
  Range("period") = Range("period") + 20
  Range("period_added") = True
  Else
  MsgBox "wrong password", 48, "TITLE"
  End If
Else
MsgBox "Time already added", 48, "TITLE"
End If
End Sub

kind regards,
Erik

Map1.xls
	A	B	C	D
1	release	period	expiredate	period_added
2	12-1-05	90	12-4-05	TRUE
Blad1

 

[erik.van.geit]

Smitty,

Just a little thought on

Note that it's about as foolproof as relying on system time as it can be defeated by not enabling macros.

You can force the user to open the workbook with macros.
in the Workookb_Close you make all sheets except one veryhidden.
those sheet displays a message
"OPEN WITH MACROS"
"else you'll get no functionality"
the sheets only reappear when opening with macros

nothing is 100% fulproof but for intermediate users it seems enough

kind regards,
Erik

 

[ikjohnson]

Thanks again for the suggestions. There should be enough detail in these posts to enable me to come up with a result.

 

[erik.van.geit]

OK!

If you find something else, let us know!

kind regards, succes with your application,
Erik