Using the OnTimer event to lock out users between specific time window.

[Living the Dream]

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.

 

[Living the Dream]

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.

 

[GS]

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

 

[Living the Dream]

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.

 

[Ben McClave]

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

 

[GS]

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

 

[GS]

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

 

[GS]

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

 

[GS]

GS made a typo:

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(gsAllowedUsers, 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

 

[Living the Dream]

Ben & Garry

This is way cool, thanks heaps.

Question though, do I put this in the Historical File with a Call to the
module from the OnOpen Workbook.

As User Jim will be accessing the Historical File via a VB Process that
copies data from his file into the Historical, I would assume that it
needs to be in the Historical, but ! then again, assumption is something
that has always got me in trouble...

Cheers
Mick.

 

[GS]

Living the Dream has brought this to us :

Ben & Garry

This is way cool, thanks heaps.

Question though, do I put this in the Historical File with a Call to the
module from the OnOpen Workbook.

As User Jim will be accessing the Historical File via a VB Process that
copies data from his file into the Historical, I would assume that it needs
to be in the Historical, but ! then again, assumption is something that has
always got me in trouble...

Cheers
Mick.

IMO, if the Historical File is where you need to restrict access then
that's where you should put it.

I use a different approach whereby the allowed user is permited to open
a password protected file for making their entries. This is tied in
with the admin utilities process I mentioned earlier. Since the
scenario of admin users and non-admin users is fairly common to me,
I've developed a standardized process I use for all such client
projects. Clearly this adds complexity to any project but I find having
an established reusable mechanism worth its weight in gold when clients
ask for multi-level user access.

I'd be happy to explain how it works if you're interested in having a
reusable methodology for handling multi-level user access to project
features, and so post back confirming your interest.

--
Garry

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

 

[Living the Dream]

As always Garry, you never cease to provide top rate help.

If I were the Guru in charge of the I.T. Dept, I would most certainly
implement your suggestion, alas, I'm a lowly desk jock who happens to be
just a little brighter than the rest of my co-workers when it comes to
excel, the I.T. Manager is super Anal when it comes to his
Administrative Domain and hence there is no flexibility.

When I get to work I will look at running your and Ben's idea's and see
how it goes.

Thanks once again.

Cheers
Mick.

 

[GS]

Living the Dream was thinking very hard :

As always Garry, you never cease to provide top rate help.

If I were the Guru in charge of the I.T. Dept, I would most certainly
implement your suggestion, alas, I'm a lowly desk jock who happens to be just
a little brighter than the rest of my co-workers when it comes to excel, the
I.T. Manager is super Anal when it comes to his Administrative Domain and
hence there is no flexibility.

When I get to work I will look at running your and Ben's idea's and see how
it goes.

Thanks once again.

Cheers
Mick.

Thanks for the feedback, Mick! As always, I'm happy to help
whenever/wherever I can...

--
Garry

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