Thread: Alternative to Inkey, Timer functions for auto log off

Hi,

I need to incorporate some vba code which will close my workbook if no key or mouse button has been pressed for about 5 minutes. I'm told that Inkey and Timer functions are not available in VBA. Is there any way to work around and solve this problem.

Thanks in advance,
Bala

Good afternoon Balachan56

Originally Posted by Balachan56

I need to incorporate some vba code which will close my workbook if no key or mouse button has been pressed for about 5 minutes.

Have a look at this post.

HTH

DominicB

Hi,

My basic idea is to close the workbook if there has been no entry made within the last 10 seconds. Each time an entry is made, the sheetchange procedure would be triggered, resulting in a different value for the downtime and hence BB. I find that the program gets hung at Closeapp() :If AA = BB line since on checking, BB is shown = 0. Earlier I used the syntax
If Now= downtime .
However, both ways I'm stuck. My use of the Public BB declaration does not seem to help. Has it been done wrongly? Should downtime be Public too?

I tried some example code suggested by Dominic in this thread, but the ontime procedure just didn't detect workbook entries and closed down on expiry of the set duration.

Can anyone suggest what's wrong and more importantly, a way out?

Thanks in advance,
Bala

'''''''''''place in Thisworkbook

Code:

Public BB as string
Private Sub Workbook_Open()        

downtime = Now + TimeValue("00:00:10")
BB = Str(downtime)

Application.OnTime downtime, Procedure:="Closeapp", Schedule:=True
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 
 downtime = Now + TimeValue("00:00:10")
 BB = Str(downtime)
 Application.OnTime downtime, Procedure:="closeapp", Schedule:=True
 
End Sub

'''''''''''place in Module

Code:

Public Sub closeapp()

AA = Str(Now)
AA = Str(downtime)
If AA = BB Then
ActiveWorkbook.Close
End If

End Sub

hi Bala,

Hi Rob,
I need some help with the use of 'ontime' and the passing of the value of a variable from thisworkbook to a macro. I've posted the code and details on...
Basically, I am trying to create and Auto log off option so that the workbook closes if no entry has been made for say 10 secs (5 mins in practice).
I need to know 1. if my way of declaring a variable Public is correct or required at all.
2.I need to know how to close any instance of 'ontime' that happens to be running, except the most recent one.
Since I got only one reply, on posting this on the site and I'm not sure if its because I've posted in the wrong category (RB: I think you're in the right category), I'm contacting you directly for advice. Kindly view the thread mentioned above. Hope you can help.
Thanks in advance,
Bala

I've spotted your visitor message to me & I can have a look after a small fix on your behalf...
Can you please edit post 5 by adding the necessary code tags?

Thanks
Rob

hi Bala,
Thanks for adding the correct code tags to post 5

Here is an example file using VBA Noob's code from post 7 of http://www.excelforum.com/excel-prog...-workbook.html

Download the file, open it & sit back & see what happens...

Regarding your code & questions:
- It's good practice to give your variables more meaningful names than "AA" & "BB".
- 1) I'm not that good at understanding/explaining the scope of variables but this explanation of Chip's may help you: http://www.cpearson.com/excel/Scope.aspx
(I should have a thorough read myself! )
- 2) Check the VBE Help Files for "Ontime", Excel 2007 states:

Syntax
expression.OnTime(EarliestTime, Procedure, LatestTime, Schedule)
expression A variable that represents an Application object.

Parameters
Name Required/Optional Data Type Description
EarliestTime Required Variant The time when you want this procedure to be run.
Procedure Required String The name of the procedure to be run.
LatestTime Optional Variant The latest time at which the procedure can be run. For example, if LatestTime is set to EarliestTime + 30 and Microsoft Excel is not in Ready, Copy, Cut, or Find mode at EarliestTime because another procedure is running, Microsoft Excel will wait 30 seconds for the first procedure to complete. If Microsoft Excel is not in Ready mode within 30 seconds, the procedure won’t be run. If this argument is omitted, Microsoft Excel will wait until the procedure can be run.
Schedule Optional Variant True to schedule a new OnTime procedure. False to clear a previously set procedure. The default value is True.

hth
Rob

Hi Rob,
Thanks for the vbe defns. I solved the problem with the following correction after removing all lines containing AA or BB.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Application.OnTime downtime, Procedure:="Closeapp", Schedule:=False
downtime = Now + TimeValue("00:00:10")
Application.OnTime downtime, Procedure:="closeapp", Schedule:=True

End Sub

This does not detect special function keys, however, it serves my purpose.

Thanks
Bala

hi Bala,

This does not take detect special function keys...

What special function keys?

Have you looked at the example file I uploaded?
This file uses "Workbook_SheetCalculate" & "Workbook_SheetSelectionChange" subs to test more thoroughly for "inactivity".

btw, if I have helped at all, please express thanks by clicking on the blue scales to add to my reputation - it is appreciated

hth
Rob

okay, thanks for the feedback, marking as solved & adding to my rep

Rob