Time limited to open exel file

Peterko

New Member
Joined
Jul 31, 2007
Messages
7
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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.

Code:
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

New Member
Joined
Jul 31, 2007
Messages
7
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

New Member
Joined
Jul 31, 2007
Messages
7
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

New Member
Joined
Jul 31, 2007
Messages
7
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.

Code:
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

New Member
Joined
Jul 31, 2007
Messages
7
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.

Code:
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

New Member
Joined
Jul 31, 2007
Messages
7
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Jul 31, 2007
Messages
7
"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?
 

Forum statistics

Threads
1,181,730
Messages
5,931,701
Members
436,799
Latest member
BasOo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top