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!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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

D
  • Question
weighted slope intercept adjustment between two different value sets
Replies
0
Views
250
Dspace
D
D
  • Question
Pricing Model change log
Replies
2
Views
132
Ducklings23
D
K
  • Question
Using VBA to select a color coded cell
Replies
0
Views
102
karl10220
K
Data123
  • Solved
Symbol list in Spreadsheet
Replies
7
Views
310
Peter_SSs
Peter_SSs
R
  • Question
Help to sum all cells above until blank cell
Replies
3
Views
265
RemyMoo
R

Forum statistics

Threads
1,216,030
Messages
6,128,418
Members
449,449
Latest member
Quiet_Nectarine_

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