Date sensitive workbook.

[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

 

[JLGWhiz]

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.

 

[Dave Peterson]

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

 

[JLGWhiz]

Yeah, I need to let my brain catch up with the logic.

 

[eighthman11]

Yeah, I need to let my brain catch up with the logic.







- Show quoted text -

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?

 

[Dave Peterson]

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.

 

[eighthman11]

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 -

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.