Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the OnTimer event to lock out users between specific time window.
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the OnTimer event to lock out users between specific time window.
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the OnTimer event to lock out users between specific time window.
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the OnTimer event to lock out users between specific time window.
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the OnTimer event to lock out users between specific time window.
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the OnTimer event to lock out users between specific time window.
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the OnTimer event to lock out users between specific time window.
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the OnTimer event to lock out users between specific time window.
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the OnTimer event to lock out users between specific time window.
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the OnTimer event to lock out users between specific timewindow.
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the OnTimer event to lock out users between specific time window.
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the OnTimer event to lock out users between specific timewindow.
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the OnTimer event to lock out users between specific time window.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lock a cell from users but not macros | Excel Programming | |||
Question About triggering a one time event based on a specific cell value | Excel Programming | |||
System Lock Event on excel | Excel Programming | |||
How do I lock a worksheet while allowing users to add comments? | Excel Discussion (Misc queries) | |||
OnTimer Event | Excel Programming |