Workbook Expiration

D

doosey2

New Member
Joined
Jun 8, 2006
Messages
20
I have an excel workbook which contains 2 worksheets: a pricing tool and a pricing log. The pricing tool takes the data input from the user and runs a macro to produce a price (sheet 1). Then, the date, customer name, and price are stored on the pricing log (sheet 2).

Currently, I am updating the pricing tool every week, and want my sales teams to use only the most current pricing tool. Also, I want my sales teams to send me back the pricing log so I can keep track of all of the quotes being made.

I have found a code to self-destruct a workbook, however, that is not my goal. I want to put an expiration date on a workbook for 1 week so that after 1 week my sales team can no longer access the pricing tool. Moreover, I want the workbook sent back to me so I can reveiw sheet 2, the pricing log.

Is there any way to put an expiration on the workbook which will prevent my sales team from using it but still allow me to open the pricing log? My idea is to possibly put an expiration on the pricing tool worksheet if this is possible, so that this piece will expire after 1 week, while the pricing log worksheet stays intact.

Is this possible?

Thanks so much in advance for your help!
 
Sort by date Sort by votes
Before you send out the Workbook, change the Expire date to the date your data expires.

This code must go in the "ThisWorkbook" code module only!

Change the Sheet name to the name of the sheet that has your log!


Private Sub Workbook_Open()
'ThisWorkbook module code, only!
Dim myExpire

myExpire = #7/1/2006#

If Date > myExpire Then Sheets("Sheet2").Select
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'ThisWorkbook module code, only!
Dim myExpire

myExpire = #7/1/2006#

If Date > myExpire Then Sheets("Sheet2").Select
End Sub
 
Upvote 0
Thanks!

Thank you so much for your help. That macro is brilliant. My only concern - if the user disables macros from the start, will this be a problem? Is there another macro I can write which will force the user to enable macros?
 
Upvote 0
Help!

Thanks again for all of your help with my problem. I've found the solution to be very clever, however, I found a minor problem with it and was wondering if we can fix it.

In the VB code, I set the expiration date for July 7, 2006. However, this date is based on the Windows clock. The Windows clock can be changed anytime by the user. In theory, a user can access the file whenever he/she wants just by changing the Windows date.

Is there a way to base the expiration date on an internal clock which cannot be altered by the user? Alternatively, is it possible to write a program for a "count down" timer in excel?

Thank you so much!
 
Upvote 0
You can set the PC time with:

Time Statement Example
This example uses the Time statement to set the computer system time to a user-defined time.

Dim MyTime
MyTime = #4:35:17 PM# ' Assign a time.
Time = MyTime ' Set system time to MyTime.


But, I do not know of an Event that will trap when a user does it by hand?



You can do a countdown timer a few way,

Here is a display way:

http://www.mrexcel.com/board2/viewtopic.php?t=98916

And this:


Sub CountDown()
Dim pausetime As Single
Dim start As Single
Dim finish As Single
Dim totaltime As Single

pausetime = 12 ' Set duration.
start = Timer ' Set start time.

Do While Timer < start + pausetime
DoEvents ' Yield to other processes.
Sheets(1).Range("B2").Value = _
Format(pausetime + (start - Timer), "##")
Loop

finish = Timer ' Set end time.
totaltime = finish - start

MsgBox "All Done!"
End

End Sub



And you can find more if you search the Board:
 
Upvote 0
You must log in or register to reply here.

Similar threads

sanantonio
  • Solved
Excel to recommend 8 "Price Groups" from range of prices
Replies
9
Views
173
NdNoviceHlp
NdNoviceHlp
PIsabel
  • Question Question
Change prices
Replies
3
Views
120
smozgur
smozgur
B
  • Question Question
Macro Assistance needed,
Replies
3
Views
85
Sam_D_Ben
Sam_D_Ben
O
  • Question Question
Formula to calculate pricing per row after total goes over threshhold
Replies
4
Views
104
Dave Patton
D
T
  • Question Question
Formula to return info from one workbook to another based off a specific column
Replies
0
Views
84
tuttlemom
T

Share this page

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
Back
Top