I have to agree with JE, it is not easy to make your file fully secure and
hidden. However, assuming that you are only trying to prompt your
distributors to use up-to-date data, rather than trying to prevent them
having any access whatsoever to previous data, then suggestions you receive
here should suffice. As Otto says, there's no way to prevent a user from
changing their system date.

Anyway, I'm sure you're not after that level of security, let's face it,
you're not going to prevent a user taking a screen shot, or even writing down
all the information and recreating the file themselves. Hopefully the code
given by Otto or me will suit your purposes.

Sean.
--
(please remember to click yes if replies you receive are helpful to you)


"SeanC UK" wrote:

Here is one way to do it. This will hide a sheet called Sheet1, you can
repeat this for as many sheets as necessary, but you must allow one sheet to
remain visible, so if necessary leave a blank sheet.

Sheet1 is hidden as the workbook closes, and if macros are enabled then it
is shown as the workbook opens, unless the date in the macro is passed.

I have used the workbook protection to prevent the sheet being copied to
another book.

You can refer to a date in a worksheet instead of the date as entered in the
code, but the user could then change this.

You will need to protect the VBA code to make this fully secure, in the VBA
editor Tools VBAProject Properties, Protection tab, and tick Lock project
for viewing.

The following code needs to go in the ThisWorkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Worksheets("Sheet1").Visible < xlSheetHidden Then
ThisWorkbook.Unprotect Password:="abc"
ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetHidden
ThisWorkbook.Protect Password:="abc", Structu=True
ThisWorkbook.Save
End If
End Sub

Private Sub Workbook_Open()
If Date = DateSerial(2007, 10, 30) Then
MsgBox ("Date passed, new workbook required")
Else
ThisWorkbook.Unprotect Password:="abc"
ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetVisible
ThisWorkbook.Worksheets("Sheet1").Activate
ThisWorkbook.Protect Password:="abc", Structu=True
ThisWorkbook.Saved = True
End If
End Sub

I hope this helps,

Sean.



--
(please remember to click yes if replies you receive are helpful to you)


"Balcott" wrote:

Using Excel 2003.
Have developed an Excel file that includes current product price list for
use by distributors of our products .
We want an auto-execute macro that will render the file inactive or
inoperable once the price list expiry date is reached. New file then provided
to active distributors.