Hi Guy’s

Was wondering if it is possible, using the OnTimer Event in Workbook
OnOpen() whereby if a user tries to manually open an excel file titled
"Historical" between a specific time window ( 3.00pm to 4.00pm ) it will
not open, and instead pop up a message advising they cannot access the
file during this period.

One user needs total uninterrupted access to the "Historical" file
between this very important period of time, and no matter how many times
you circulate emails to other departments asking people to not have it
open during this time they just don’t listen or don’t care.

I am currently using the OnTimer Event to automatically close the
"Historical" File after 10 mins so other users do not inadvertently
leave it active.

As an interim prevention, I have also just changed the "Historical"
Files Attribute Property to ReadOnly to stop other users from altering
the Filters & Layout.

I have inserted some code behind the Main User's ( who needs access to
the "Historical" File ) Main File he works from which changes the
"Historical" File to Normal just prior to opening, then back to ReadOnly
when he is finished.

It may appear to be a case of overkill, but it’s the only way I can
educate people and stop them from preventing this very integral function
from taking place during the above time period.

Any assistance is appreciated

Cheers
Mick.

Hi guy's

Seems this may not be possible as yet no takers..

I was thinking along the lines of this, but unfortunately it does not
work, maybe someone could help steer me in the right direction
please..

Private Sub Workbook_Open()

Dim OpenTime As String
Dim resp As Long

OpenTime = Format(Now, "h:mm:ss AM/PM")

If OpenTime < #4:01:00 PM# #2:29:00 PM# Then

resp = MsgBox("YOU ARE NOT ALLOWED ACCESS BETWEEN 2.30 & 4.00PM
DAILY - PLEASE TRY LATER", vbOKOnly)
ThisWorkbook.Close SaveChanges:=False

End If

End Sub


It would so cool if Excel had the same BeforeOpen() function as does
Access...

In the event anybody happens to have a work-around for the above, I
will then need a way using VB to bypass the above so the main user who
requires unfetted access between the above times can access it.

Guy's

If this is pie-in-the-sky and not possible, please let me know and I
will stop wasting time on it and look for another alterative.

Appreciate any assistance or comments.

TIA
Mick.

Living the Dream formulated on Wednesday :
Hi guy's

Seems this may not be possible as yet no takers..

I was thinking along the lines of this, but unfortunately it does not
work, maybe someone could help steer me in the right direction
please..

Private Sub Workbook_Open()

Dim OpenTime As String
Dim resp As Long

OpenTime = Format(Now, "h:mm:ss AM/PM")

If OpenTime < #4:01:00 PM# #2:29:00 PM# Then

resp = MsgBox("YOU ARE NOT ALLOWED ACCESS BETWEEN 2.30 & 4.00PM
DAILY - PLEASE TRY LATER", vbOKOnly)
ThisWorkbook.Close SaveChanges:=False

End If

End Sub


It would so cool if Excel had the same BeforeOpen() function as does
Access...

In the event anybody happens to have a work-around for the above, I
will then need a way using VB to bypass the above so the main user who
requires unfetted access between the above times can access it.

Guy's

If this is pie-in-the-sky and not possible, please let me know and I
will stop wasting time on it and look for another alterative.

Appreciate any assistance or comments.

TIA
Mick.

Mick,
You need to use the *And* operand in your If...Then construct...

If (OpenTime < #4:01:00 PM#) And (OpenTime #2:29:00 PM#) Then

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry

Thanks heaps for that, it works great.

Can I pick your brain how to best accomplish the following:

I need using VB from another workbook to sidestep this so that the
intended user can access during this time period.

I was toying with the idea of adding a password protocol to bypass the
pop up.

the password could be stored in a Cell on his main file and the
Historical file could look at the Cells Value to a see if it matches
the one entered into the pop up password field which would allow him
to continue and others hanging in limbo...

Appreciate your thoughts.

Cheers
Mick.

Mick,

I'm not sure if it would work in your environment, but perhaps the Environ function would help. First, add this function to your workbook:

Function AllowAccess(sUser As String) As Boolean

Select Case sUser
Case "ajones", "jsmith" 'Customize list as needed
AllowAccess = True
Case Else
AllowAccess = False
End Select

End Function

Then, use this line to run the function:

AllowAccess (Environ("username"))

If the user's username is on the list, the function will return TRUE.

Good Luck,

Ben

Hi Mick,
Ben has a good suggestion that's much simpler to implement than my
login process for 'special' users. I'd eliminate the 'Else' part of his
code, though, since the return is false by default and so extra
processing is not required...

Const sAllowedUsers$ = "ajones,jsmith" '//edit to suit

Function AllowAccess2(sUser$) As Boolean
If InStr(sAllowedUsers, sUser) 0 Then AllowAccess = True
End Function 'AllowAccess

My process enables enhanced menuitems for admin users only via an
additional xla file as most of my stuff ships as 'core' apps with basic
user functionality. The admin component is usually shipped as a
'plugin' to be stored in the main app's 'Plugins' folder on machines
used (or login) by admin groups with special priveleges. (This means my
addins have addins!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Optimized version...

Const sAllowedUsers$ = "ajones,jsmith" '//edit to suit

Function AllowAccess2(sUser$) As Boolean
AllowAccess = InStr(sAllowedUsers, sUser) 0
End Function 'AllowAccess

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Since allowing access is conditional, Ben`s call line could be modified
as follows to obviate need for a function...

In a standard module where globals are stored:
Public Const gsAllowedUsers$ = "ajones,jsmith" '//edit to suit

SomeSub()
If InStr(gsAlloweUsers, Environ("username")) 0 Then _
`do stuff
End Sub 'SomeSub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion