Date sensitive workbook.

  • Thread starter Thread starter eighthman11
  • Start date Start date
E

eighthman11

Hello everyone:
I have a spreadsheet which needs to be refreshed with current data
periodically. This spreadsheet is used by people at remote sites that
are not connected to our companies data. So I email them the raw data
and they import this data to the spreadsheet through VB code.

Now for the question. Is there a way I can create a timed event that
will force the user to refresh the data and if they do not in the time
period it makes the spreadsheet disabled. I would like for them to
refresh the data every three months. Would also like to give them a
warning message that they need to do a refresh before the spreadsheet
actually becomes disabled.

Is this possible are a pretty crazy idea? Any help and or examples
appreciated. Thanks Ray: ps using Excel 2000 on XP
 
There might be a better way, but you could use the Workbook_Open event to
check the current date and if it is three months from the issue date then
flash a message box.

Private Sub Workbook_Open()
IssueDate = 1/31/2008
If Date >= IssueDate + 90 Then
MsgBox "Refresh Is Due!", , "Advisory"
End If
End Sub

If will check each time the file is opened but will not show the message box
until the 90 day period is reached or exceeded.
 
I bet you wanted IssueDate to be a date:

Private Sub Workbook_Open()
Dim IssueDate as Date
IssueDate = dateserial(2008,1,31)
If Date >= IssueDate + 90 Then
MsgBox "Refresh Is Due!", , "Advisory"
End If
End Sub
 
Yeah, I need to let my brain catch up with the logic.







- Show quoted text -
Click to expand...

Thanks for the help. Based on your suggestions I got it working just
the way I want. One minor problem.
If I get a user who is savy enough to change the system date on the
computer they could bypass having
to update the spreadsheet. Any Ideas?
 
The same person will find a way to avoid the autorun macros.

I think you're fighting a losing battle.

But if you're a glutton for punishment and the user is connected to the
internet, you could search google for retrieving the date from a trusted source.
 
The same person will find a way to avoid the autorun macros.

I think you're fighting a losing battle.

But if you're a glutton for punishment and the user is connected to the
internet, you could search google for retrieving the date from a trusted source.





eighthman11wrote:



--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks Dave. I agree, you can only do so much. At some point the
user has to take some
responsibility that they are doing their job correctly. Once again
thanks for the help.
 
Back
Top