S
Sean
I have the following code which displays a sheet called E-Splash if
you are not a user listed in named range MyUsers
How can I add to this that a second condition would be applied that if
you are listed also in MyUsers2 then unless A1 in sheet E-Users is
within 35 days of Now() you can't access the file and a message as
follows appears "Time Expired, Please contact etc etc" This would also
mean that if you are in MyUsers and not in MyUsers2 you could access
the file not matter how far Now() is from A1
Thanks
Private Sub Workbook_Open()
Dim myArray As Variant
Dim arName As String
Dim ws As Worksheet
arName = "MyUsers"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value
With Application
If IsError(.Application.Match(.UserName, myArray, 0)) Then
MsgBox "You are NOT Permitted to access this File " & vbCr & _
"" & vbCr & _
"Please Contact Joe Bloggs at " & vbCr & _
"" & vbCr & _
"ABC Inc +0099 1 234567"
Application.DisplayAlerts = False
ThisWorkbook.Close False
Else
For Each ws In Worksheets
ws.Visible = True
Next
Worksheets("E-Splash").Visible = False
Worksheets("E-Users").Visible = xlVeryHidden
Worksheets("E-Sum").Activate
Application.DisplayAlerts = True
End If
End With
End Sub
you are not a user listed in named range MyUsers
How can I add to this that a second condition would be applied that if
you are listed also in MyUsers2 then unless A1 in sheet E-Users is
within 35 days of Now() you can't access the file and a message as
follows appears "Time Expired, Please contact etc etc" This would also
mean that if you are in MyUsers and not in MyUsers2 you could access
the file not matter how far Now() is from A1
Thanks
Private Sub Workbook_Open()
Dim myArray As Variant
Dim arName As String
Dim ws As Worksheet
arName = "MyUsers"
myArray = ThisWorkbook.Names(arName).RefersToRange.Value
With Application
If IsError(.Application.Match(.UserName, myArray, 0)) Then
MsgBox "You are NOT Permitted to access this File " & vbCr & _
"" & vbCr & _
"Please Contact Joe Bloggs at " & vbCr & _
"" & vbCr & _
"ABC Inc +0099 1 234567"
Application.DisplayAlerts = False
ThisWorkbook.Close False
Else
For Each ws In Worksheets
ws.Visible = True
Next
Worksheets("E-Splash").Visible = False
Worksheets("E-Users").Visible = xlVeryHidden
Worksheets("E-Sum").Activate
Application.DisplayAlerts = True
End If
End With
End Sub