Time limited to open exel file

[Peterko]

HI,

I have one excel file which I plan to send to other person. But I need this file to be openable/valid by this person just 20 days after first opening of the file.
After 20 days this person will not be able to open the file any more.

Any suggest, please?
Thanks in advance.

Peters

 

[HalfAce]

Hello Peterko,
Sure, this can be done using vba. It would require the 'end user' to either have their
security setting set to allow macros to be enabled, or they'll have to allow them to be
enabled when the workbook gets opened.

There are ways to force the user to enable macros (these can be found by searching
this board - or we can help with that).

Does that sound acceptable to you? If so, what do you want to have happen (20 days? -
more than 20 days?) after the first opening?
(ie.) Some kind of message? Delete all data in the workbook? Just close back down? ...(?)

 

[Jonmo1]

Create a hidden sheet (actually xlveryhidden would be best) - name that sheet something like "Log". I'll explain how to do xlveryhidden later if you don't already know how...

right click the little excel icon in top left cornder by "file - edit - etc.." View code. This opens the VBA window

Paste the following code in the little white window on the right.

Private Sub Workbook_Open()
If Sheets("Log").Range("A1") = "" Then
    Sheets("Log").Range("A1") = Date + 20
    ActiveWorkbook.Save
End If

If Date >= Sheets("Log").Range("A1") Then
    MsgBox "This Book Is Expired"
    applicaiton.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End If
    
End Sub

Save and close the file.



to make a sheet xlveryhidden -

right click the sheet tab, view code
click on view - properties window
look for the visible property, and set to xlveryhidden

 

[Peterko]

Answer1

Hello HalfAce,

"...It would require the 'end user' to either have their
security setting set to allow macros to be enabled, or they'll have to allow them to be enabled when the workbook gets opened. "
---

WE CAN DO IT MORE SIMPLY, IT IS NOT NECESSARY TO DEFINE END USER. JUST EXCEL FILE WILL BE VALID for example 20 DAYS AFTER FIRST OPENING THE FILE...
BUT YOUR SUGGEST WOULD BE PERFECT, OF COURSE... if it is not very difficult for you.
---

"There are ways to force the user to enable macros (these can be found by searching this board - or we can help with that). "
---

YES, IT IS IMPORTANT TO FORCE THE USER TO ENABLE MACROS. THE FINAL MACRO CODE SHOULD INCLUDE THAT, IF YOU CAN DO IT, IT IS GREAT!
---

"Does that sound acceptable to you? If so, what do you want to have happen (20 days? -
more than 20 days?) after the first opening?
(ie.) Some kind of message? Delete all data in the workbook? Just close back down? ...(?)"
---

YES, THAT SOUND ACCEPTABLE FOR ME. AFTER FIRST OPENING, OTHER PERSON WILL SEE SOME TABLES AND AFTEER 20 DAYS THE FILE WILL NOT BE ABLE TO OPEN, MAYBE WE CAN DELETE ALL DATA AND SHOW SOME TEXT: "VALIDITY ends..." or something like that...

I PREFER ALL CODE IF IT IS POSSIBLE.
THANKS IN ADVANCE!

 

[Peterko]

Answer1 for HalfAce

Hello HalfAce,

"...It would require the 'end user' to either have their
security setting set to allow macros to be enabled, or they'll have to allow them to be enabled when the workbook gets opened. "
---

WE CAN DO IT MORE SIMPLY, IT IS NOT NECESSARY TO DEFINE END USER. JUST EXCEL FILE WILL BE VALID for example 20 DAYS AFTER FIRST OPENING THE FILE...
BUT YOUR SUGGEST WOULD BE PERFECT, OF COURSE... if it is not very difficult for you.
---

"There are ways to force the user to enable macros (these can be found by searching this board - or we can help with that). "
---

YES, IT IS IMPORTANT TO FORCE THE USER TO ENABLE MACROS. THE FINAL MACRO CODE SHOULD INCLUDE THAT, IF YOU CAN DO IT, IT IS GREAT!
---

"Does that sound acceptable to you? If so, what do you want to have happen (20 days? -
more than 20 days?) after the first opening?
(ie.) Some kind of message? Delete all data in the workbook? Just close back down? ...(?)"
---

YES, THAT SOUND ACCEPTABLE FOR ME. AFTER FIRST OPENING, OTHER PERSON WILL SEE SOME TABLES AND AFTEER 20 DAYS THE FILE WILL NOT BE ABLE TO OPEN, MAYBE WE CAN DELETE ALL DATA AND SHOW SOME TEXT: "VALIDITY ends..." or something like that...

I PREFER ALL CODE IF IT IS POSSIBLE.
THANKS IN ADVANCE!

 

[Peterko]

Thanks, it sounds good too.
---

Create a hidden sheet (actually xlveryhidden would be best) - name that sheet something like "Log". I'll explain how to do xlveryhidden later if you don't already know how...

right click the little excel icon in top left cornder by "file - edit - etc.." View code. This opens the VBA window

Paste the following code in the little white window on the right.

Private Sub Workbook_Open()
If Sheets("Log").Range("A1") = "" Then
    Sheets("Log").Range("A1") = Date + 20
    ActiveWorkbook.Save
End If

If Date >= Sheets("Log").Range("A1") Then
    MsgBox "This Book Is Expired"
    applicaiton.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End If
    
End Sub

Save and close the file.



to make a sheet xlveryhidden -

right click the sheet tab, view code
click on view - properties window
look for the visible property, and set to xlveryhidden

 

[Peterko]

Thanks, it sounds good too.
---

Create a hidden sheet (actually xlveryhidden would be best) - name that sheet something like "Log". I'll explain how to do xlveryhidden later if you don't already know how...

right click the little excel icon in top left cornder by "file - edit - etc.." View code. This opens the VBA window

Paste the following code in the little white window on the right.

Private Sub Workbook_Open()
If Sheets("Log").Range("A1") = "" Then
    Sheets("Log").Range("A1") = Date + 20
    ActiveWorkbook.Save
End If

If Date >= Sheets("Log").Range("A1") Then
    MsgBox "This Book Is Expired"
    applicaiton.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End If
    
End Sub

Save and close the file.



to make a sheet xlveryhidden -

right click the sheet tab, view code
click on view - properties window
look for the visible property, and set to xlveryhidden

 

[Peterko]

I tried it, but have problem...

I created "Log" sheet + pasted the code.

I wanted to test it, so first I did not hide Log sheet. I suppose, something should be in A1 cell, but nothing displayed?
Then I change the date, as we open it after 20 days, and it did not work.
First sheet has name: "Sheet1" - I am not sure, if it can be problem?

Can you help me more + explai the code in more detaile?

 

[Jonmo1]

I suppose, something should be in A1 cell, but nothing displayed?

when you first save the filie, A1 should be EMPTY.

so that when it is first opened by your intened user, the code will put the date in that cell.

future times it is opened, it will not CHANGE the date in A1. But will evaluate if todays' date is = or greater than that date.

 

[Peterko]

"when you first save the filie, A1 should be EMPTY."

YES, I UNDERSTAND...
---

so that when it is first opened by your intened user, the code will put the date in that cell.

AFTER FIRST SAVE I OPENED THE FILE TO CHECK VALUE IN A1, BUT I CANNOT SEE DATE IN THAT CELL. NOTHING DISPLAYS
---

"future times it is opened, it will not CHANGE the date in A1. But will evaluate if todays' date is = or greater than that date."

YES, I UNDERSTAND...

ONLY PROBLEM IS, AFTER FIRST OPEN OF EXCEL FILE, THERE IS NOTHING IN CELL A1. ALSO, WHEN I CHANGED SYSTEM DATE, AS IT IS AFTER 20 DAYS, I CAN OPEN FILE WITHOUT ANY PROBLEM

What can be wrong?