Date sensitive workbook.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

eighthman11 - 26 Mar 2008 20:15 GMT

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

Reply to this Message

JLGWhiz - 26 Mar 2008 20:48 GMT

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.

> Hello everyone:
> I have a spreadsheet which needs to be refreshed with current data
[quoted text clipped - 11 lines]
> Is this possible are a pretty crazy idea? Any help and or examples
> appreciated. Thanks Ray: ps using Excel 2000 on XP

Reply to this Message

Dave Peterson - 26 Mar 2008 21:53 GMT

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

> 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
[quoted text clipped - 25 lines]
> > Is this possible are a pretty crazy idea? Any help and or examples
> > appreciated. Thanks Ray: ps using Excel 2000 on XP

Signature

Dave Peterson

Reply to this Message

JLGWhiz - 26 Mar 2008 22:33 GMT

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

> I bet you wanted IssueDate to be a date:
>
[quoted text clipped - 35 lines]
> > > Is this possible are a pretty crazy idea? Any help and or examples
> > > appreciated. Thanks Ray: ps using Excel 2000 on XP

Reply to this Message

eighthman11 - 27 Mar 2008 15:52 GMT

> Yeah, I need to let my brain catch up with the logic.
>
[quoted text clipped - 43 lines]
>
> - 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?

Reply to this Message

Dave Peterson - 27 Mar 2008 16:29 GMT

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.

> > Yeah, I need to let my brain catch up with the logic.
> >
[quoted text clipped - 49 lines]
> computer they could bypass having
> to update the spreadsheet. Any Ideas?

Signature

Dave Peterson

Reply to this Message

eighthman11 - 27 Mar 2008 16:48 GMT

> The same person will find a way to avoid the autorun macros.
>
[quoted text clipped - 64 lines]
>
> - 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.

Reply to this Message