• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Set trial period

vrunda

vrunda

Member
I have made an add-in . I want to set trial period for 30 days .

What should be the necessary code.?
 
This is one way if the users do not know registry and their way around. However the issue with any VBA solution is that it can be cracked one way or the other.


http://spreadsheetpage.com/index.php/site/tip/using_the_getsetting_savesetting_functions/
 
I saw Cpearson.com- - timebomb workbook . But For Add-in where to write the code Eg in module or Thisworkbook code area. Secondly- - ExpirationDate = Mid(ThisWorkbook.Names("ExpirationDate").Value, 2) >>>>Here ThisWorkbook is used in code so for ADD-IN WHAT SHOULD i Write?

And finally to test it for a short time Can we use instead of 30 days for 10 mins. How?
 
The add-in will use the same reference structure, so you should be okay to use ThisWorkbook to refer to the Add-in iteself. Usually way of building an add-in is to create it in a workbook and then you just choose a different "save as" option. If you only want 10 mins, we just remember that 10 minutes is equal to 1 day / 24 hours / 60 minutes * 10 minutes = 1/24/60*10 = 1/144.

You could change the constant

Code: 
Private Const C_NUM_DAYS_UNTIL_EXPIRATION = 30

to be something like

Private Const C_NUM_DAYS_UNTIL_EXPIRATION = 1/144
 
I did the same but it did not work . I shall try again.

When we use date expiration date ....so is it ok if we are using for 10 mins. Coz no date in this


Private Const C_NUM_DAYS_UNTIL_EXPIRATION = 1/144
 
Oops, I missed that part where Chip's code forces the expiration into a single date. I think this will setup a 10 minutes trial period.

Code: 
Private Const C_NUM_DAYS_UNTIL_EXPIRATION = 1 / 144
Sub TimeBombWithDefinedName()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' TimeBombWithDefinedName
    ' This procedure uses a defined name to store this workbook's
    ' expiration date. If the expiration date has passed, a
    ' MsgBox is displayed and this workbook is closed.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim ExpirationDate As String
    
    On Error Resume Next
    ExpirationDate = Mid(ThisWorkbook.Names("ExpirationDate").Value, 2)
    If Err.Number <> 0 Then
        '''''''''''''''''''''''''''''''''''''''''''
        ' Name doesn't exist. Create it.
        '''''''''''''''''''''''''''''''''''''''''''
        ExpirationDate = CStr(Now + C_NUM_DAYS_UNTIL_EXPIRATION)
        ThisWorkbook.Names.Add Name:="ExpirationDate", _
        RefersTo:=Format(ExpirationDate, "short date"), _
        Visible:=False
    End If
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' If the today is past the expiration date, close the
    ' workbook. If the defined name didn't exist, we need
    ' to Save the workbook to save the newly created name.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If CDate(Now) > CDate(ExpirationDate) Then
        MsgBox "This workbook trial period has expired.", vbOKOnly
        ThisWorkbook.Close savechanges:=False
    End If

End Sub
 
Last edited:
I have 2-3 modules , So if any one module contain this code will it do?

Now i am getting msg as the macro is not available Or macros are disabled. But Macros are enabled. So is the time bomb code working. But the msg in code "This workbook trial period has expired" is not coming.
 
At the bottom of Chip's page, you'll see that he says you need to call the macro with the Workbook_Open event.

[pre]
Code: 
Private Sub Workbook_Open()
TimeBombWithDefinedName
End Sub
[/pre]
This will trigger the check to occur when the book/add-in as opened. Please read all of Chip's article as it does have several points about how the security could be bypassed. There's also an example workbook you could download and play with.
 
Ok.

But I thought it is for workbook So I ... It means Everything which applies to workbook it also applies to xlsx, xlam, xla , xlt files too.

I read that through VB6 & VStO it is better.
 
The ThisWorkbook object refers to whatever "thing" the code is contained within. Technically, it can't refer to an xlsx since those don't contain code, but for anything else that can contain code, if the code within that object refers to ThisWorkbook, that's a self reference.


Another way to think of it is that it's a nice, convenient way to refer to the specific owning object w/o having to know that name.
 
Sir ,

I enterd the code in module , Then called it from workbook open Event. I placed the add-in in addIns folder. Then opened other workbook --Book1 &

I dont know why I am getting Msg "The workbook trial period has expired" after just opening the normal workbook.


Ten minutes are yet to expire
 
Perhaps you've already run the code one and thus created the Named Formula containing an date in the past? You could test for this by running this in the Immediate Window

Code: 
ThisWorkbook.Names("expirationdate").Value

and see if a value appears.


Alternatively, if you just want to reset the Named Range, run this:

Code: 
Sub ClearName()
On Error Resume Next
ThisWorkbook.Names("ExpirationDate").Delete
End Sub
From the add-in code. This will clear out the Named Range. SAVE AT THIS POINT!


Then, the next time you open the add-in, the Workbook_Open event will trigger the macro, setting the expiration time as 10 minutes in the future. If you open the workbook after that 10 minute mark, you should get the expiration message.
 
Last edited:
Sir,

The clearName procedure should be only for testing purpose ?

Or if I put in original add-in file then Will it not clear the name everytime.
 
I run the clearname procedure , which i placed in same xlsm file, then saved it & then saved the file as xlam ...at this point itself it gave msgbox msg.

I tried from immediate window too but then also it gave error object not supported ....


I am using Win7 32- bit OS, office2010
 
Correct, the ClearName is only to wipe the ExpirationDate variable clean, and is only needed for testing.


Very strange things are going on, it sounds. Do you have any other code that's causing the macro to fire? Since it's only connected to the Workbook_Open event, I'm not sure why you're seeing the msgbox when just saving the file. I'd recommend doing some more testing with the file just as a regular workbook, maybe stepping htrough the code 1 line at a time to better understand it. Then, switch it over to an XLA and learn about how that makes a difference.
 
One strange thing I noticed. My made add-in "Vrunda" is already installed in my Laptop. For the same only I want to set trial period. For testing purpose i used the time bomb code to different add-in named "Group" xlsm. SO when i saved it as add-in & opened book1 it gives me 2 times the same msgbox of expired ----. & when I click any add-in button it runs code & userform of "vunda" add-in . I tried 3-4 times but the "Group" gives result of "Vrunda" . In short When i click buttons of group--- vrunda gets activated.I am thinking to use for 1 day & then lets see what happens?
 
Hi, vrunda!


Despite of your efforts about setting a trial period for an add-in, I'm afraid that I must be the bearer of bad news. Please give a look to this link (and there're are much more of them, you can find them using the top right search feature entering my nick and Excel protection or password as keywords):

http://chandoo.org/forums/topic/macro-to-hideunhide-in-protected-sheet-produces-error-code#post-37901


Regards!
 
Luke M Sir,


Again strange things are happening--- When i used the code for 1 day , in the book1 the add-in buttons get clicked & nothing happens. But in the xlsm file of add-in I get two same buttons (one xlsm file btn & one add-in btn --in home group I installed). There both the buttons work. This happened yesterday itself. Now today after 24 hrs i need to check what happens. I tried in other excel files too but nothing happens & add-ins xlsm file works well. Am i doing some silly mistake.?
 
Is it necessary to create name range ?

Can we not directly use dates to say expiration date is something like Now+1 or Now+1/144 etcetc. & declare Expirationdate as date
 
You need to store the variable name somewhere, unless you want to hard code the expiration date. I.e., currently the code is supposed to expire x days after you first open it. since it is originally unknown what date the workbook will first be opened, we need a variable (the named range) to store the expiration date. You could of course just pick a known date in the future and have the code run against that.
 
Luke M said:
Oops, I missed that part where Chip's code forces the expiration into a single date. I think this will setup a 10 minutes trial period.

[pre]
Code: 
Private Const C_NUM_DAYS_UNTIL_EXPIRATION = 1 / 144
Sub TimeBombWithDefinedName()
&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;
&#39; TimeBombWithDefinedName
&#39; This procedure uses a defined name to store this workbook&#39;s
&#39; expiration date. If the expiration date has passed, a
&#39; MsgBox is displayed and this workbook is closed.
&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;
Dim ExpirationDate As String

On Error Resume Next
ExpirationDate = Mid(ThisWorkbook.Names(&#34;ExpirationDate&#34;).Value, 2)
If Err.Number &#60;&#62; 0 Then
&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;
&#39; Name doesn&#39;t exist. Create it.
&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;
ExpirationDate = CStr(Now + C_NUM_DAYS_UNTIL_EXPIRATION)
ThisWorkbook.Names.Add Name:=&#34;ExpirationDate&#34;, _
RefersTo:=Format(ExpirationDate, &#34;short date&#34;), _
Visible:=False
End If

&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;
&#39; If the today is past the expiration date, close the
&#39; workbook. If the defined name didn&#39;t exist, we need
&#39; to Save the workbook to save the newly created name.
&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;&#39;
If CDate(Now) &#62; CDate(ExpirationDate) Then
MsgBox &#34;This workbook trial period has expired.&#34;, vbOKOnly
ThisWorkbook.Close savechanges:=False
End If

End Sub
[/pre]
Click to expand...
Hi Can you repost this code. for some reason i am not able to see the code.
 
Cleaned up code:
Code:
mayur553 said:
Hi Can you repost this code. for some reason i am not able to see the code.
Click to expand...
Sure thing.

Code: 
Private Const C_NUM_DAYS_UNTIL_EXPIRATION = 1 / 144
Sub TimeBombWithDefinedName()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' TimeBombWithDefinedName
    ' This procedure uses a defined name to store this workbook's
    ' expiration date. If the expiration date has passed, a
    ' MsgBox is displayed and this workbook is closed.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim ExpirationDate As String
    
    On Error Resume Next
    ExpirationDate = Mid(ThisWorkbook.Names("ExpirationDate").Value, 2)
    If Err.Number <> 0 Then
        '''''''''''''''''''''''''''''''''''''''''''
        ' Name doesn't exist. Create it.
        '''''''''''''''''''''''''''''''''''''''''''
        ExpirationDate = CStr(Now + C_NUM_DAYS_UNTIL_EXPIRATION)
        ThisWorkbook.Names.Add Name:="ExpirationDate", _
        RefersTo:=Format(ExpirationDate, "short date"), _
        Visible:=False
    End If
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' If the today is past the expiration date, close the
    ' workbook. If the defined name didn't exist, we need
    ' to Save the workbook to save the newly created name.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If CDate(Now) > CDate(ExpirationDate) Then
        MsgBox "This workbook trial period has expired.", vbOKOnly
        ThisWorkbook.Close savechanges:=False
    End If

End Sub
 
Luke M said:
Cleaned up code:
Code:

Sure thing.

Code: 
Private Const C_NUM_DAYS_UNTIL_EXPIRATION = 1 / 144
Sub TimeBombWithDefinedName()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' TimeBombWithDefinedName
    ' This procedure uses a defined name to store this workbook's
    ' expiration date. If the expiration date has passed, a
    ' MsgBox is displayed and this workbook is closed.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim ExpirationDate As String
   
    On Error Resume Next
    ExpirationDate = Mid(ThisWorkbook.Names("ExpirationDate").Value, 2)
    If Err.Number <> 0 Then
        '''''''''''''''''''''''''''''''''''''''''''
        ' Name doesn't exist. Create it.
        '''''''''''''''''''''''''''''''''''''''''''
        ExpirationDate = CStr(Now + C_NUM_DAYS_UNTIL_EXPIRATION)
        ThisWorkbook.Names.Add Name:="ExpirationDate", _
        RefersTo:=Format(ExpirationDate, "short date"), _
        Visible:=False
    End If
   
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' If the today is past the expiration date, close the
    ' workbook. If the defined name didn't exist, we need
    ' to Save the workbook to save the newly created name.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If CDate(Now) > CDate(ExpirationDate) Then
        MsgBox "This workbook trial period has expired.", vbOKOnly
        ThisWorkbook.Close savechanges:=False
    End If

End Sub
Click to expand...
Thanks Luke, really appreciate that.

For some reason, it didn't work for me. I tried with 1/144 (10 mins) and 1/72 (20mins). After i open the file the second time (almost immediately) it pops up the message that trial period has expired.
 
You must log in or register to reply here.
Back
Top