Date and User restriction coding
 

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

Date and User restriction coding
 

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
.