I use the code below to date restrict and user restrict a spreadsheet to
the best of Excel's capability. If the date fails however it continues
to repopen and makes visible the hidden sheet (Report). How can I
correct this ?


Private Sub Workbook_Open()

Dim exdate As Date
exdate = "07/12/2009"
If Date exdate Then
Sheets("Welcome Sheet").Visible = xlSheetVisible
Sheets("Report").Visible = xlSheetVeryHidden
MsgBox ("Sorry this spreadsheet has expired please use latest
version")
Application.DisplayAlerts = False
Workbooks("Master 09 17.50 VAT.XLS").Close
End If

Select Case LCase(Environ("username"))
Case Is = "USER.1", "USER.2"
Sheets("Report").Visible = xlSheetVisible
Sheets("Welcome Sheet").Visible = xlSheetVeryHidden
Sheets("Report").Select
Range("A1").Select

Case Else

Sheets("Welcome Sheet").Visible = xlSheetVisible
Sheets("Report").Visible = xlSheetVeryHidden
Application.DisplayAlerts = False
Workbooks("FILENAME.XLS").Close

End Select

End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("Welcome Sheet").Visible = xlSheetVisible
Sheets("Report").Visible = xlSheetVeryHidden

End Sub




--
PSM

after the Workbooks("Master 09 17.50 VAT.XLS").Close line (and before the end
if)
put the line
Exit Sub

This will end the routine and not perform the second check, which I am
assuming is what is unhiding the Report sheet.

--
If this helps, please remember to click yes.


"PSM" wrote:


I use the code below to date restrict and user restrict a spreadsheet to
the best of Excel's capability. If the date fails however it continues
to repopen and makes visible the hidden sheet (Report). How can I
correct this ?


Private Sub Workbook_Open()

Dim exdate As Date
exdate = "07/12/2009"
If Date exdate Then
Sheets("Welcome Sheet").Visible = xlSheetVisible
Sheets("Report").Visible = xlSheetVeryHidden
MsgBox ("Sorry this spreadsheet has expired please use latest
version")
Application.DisplayAlerts = False
Workbooks("Master 09 17.50 VAT.XLS").Close
End If

Select Case LCase(Environ("username"))
Case Is = "USER.1", "USER.2"
Sheets("Report").Visible = xlSheetVisible
Sheets("Welcome Sheet").Visible = xlSheetVeryHidden
Sheets("Report").Select
Range("A1").Select

Case Else

Sheets("Welcome Sheet").Visible = xlSheetVisible
Sheets("Report").Visible = xlSheetVeryHidden
Application.DisplayAlerts = False
Workbooks("FILENAME.XLS").Close

End Select

End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("Welcome Sheet").Visible = xlSheetVisible
Sheets("Report").Visible = xlSheetVeryHidden

End Sub




--
PSM
.