Elapsed Time Macro

R

RJQMAN

I have a worksheet that contains sensitive data. When it is used, some
of the users tend to accidentally leave the data displayed and walk
away from it, allowing the data to be read by others who pass by. I
would like to have a macro or some other means of automatically
changing the screen display to a different worksheet (or blank for that
matter) after a fixed amount of time without a keystroke - perhaps one
minute or so of elapsed time without a keystroke. Is there a practical
way to do this?
 
B

Bob Phillips

Option Explicit

'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
nElapsed = TimeSerial(0, 5, 0) '5 minutes
'start a timer to countdown inactivity
Application.OnTime Now + nElapsed, "Countdown"
End Sub

'-----------------------------------------------------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'-----------------------------------------------------------------
'any workbook activity resets the timer
Application.OnTime Now + nElapsed, "Countdown"
End Sub


'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



Put this code in a standard code module


Option Explicit

Public nElapsed As Double

'-----------------------------------------------------------------
Sub Countdown()
'-----------------------------------------------------------------
Dim oWS As Object
Dim oThisWB As Workbook
Set oThisWB = ActiveWorkbook
For i = 1 To Workbooks.Count
If Workbooks(i).Name <> oThisWB.Name Then
If Windows(Workbooks(i).Name).Visible Then
Workbooks(i).Activate
Exit For
End If
End If
Next i
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
H

hooper123

I get an compile error: Variable not defined. "i" is highlighted in the
statement For i = 1....
 
C

Chip Pearson

Did you declare the variable 'i'?

Put
Dim i As Long
at the top of your procedure.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
B

Bob Phillips

Sorry that was my fault. The standard code module code should be

Option Explicit

Public nElapsed As Double

'-----------------------------------------------------------------
Sub Countdown()
'-----------------------------------------------------------------
Dim oWS As Object
Dim oThisWB As Workbook
Dim i As Long
Set oThisWB = ActiveWorkbook
For i = 1 To Workbooks.Count
If Workbooks(i).Name <> oThisWB.Name Then
If Windows(Workbooks(i).Name).Visible Then
Workbooks(i).Activate
Exit For
End If
End If
Next i
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
R

RJQMAN

Bob, I have loaded this code as per your guidelines, but nothing
happens. I do not understand the code, so I am not able to
trouble-shoot it. At first I had the same problem that Hooper
indicated - after the system timed out, I got the error message. I
added the variable dimension i as long, and now nothing seems to
happen. Are some of these references supposed to refer to the specific
name of my workbook? I just do not understand.

Let me try to restate my goals. After some thought, I think it would
be best to have the program go to a specific worksheet (a non-sensitive
one) within the same workbook if nothing happens for a period of time.
The user could then easily switch back to the more sensitive worksheet
they desired when they returned to the computer. (This is sort of
similar to a screensaver, as suggested by someone else, but since I
cannot access their machines, I am not able to switch on a screensaver.


I apologize for my lack of knowledge, and I appreciate your help.
 
B

Bob Phillips

Maybe try this

Option Explicit

Public nElapsed As Double

'-----------------------------------------------------------------
Sub Countdown()
'-----------------------------------------------------------------
Dim oWS As Object
Dim oThisWB As Workbook
Dim i As Long
Set oThisWB = ActiveWorkbook
Worksheets("Non-sensitive").Activate
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
R

RJQMAN

This concept seems to work well - once. (Note - I reset the timer from
5 to 1 minute, as it could take the rest of my life to test it at the
five minute interval, and 1 minute seems about right for the
application too. I did this by replacing the 5 in the workbook macro
with a 1 - hopefully that did not screw anything up??)

After it has functioned correctly, from then on the timing is
unpredictable. In testing it out, after the jump, I go back to work on
the real sheets. Now, the whole thing is unpredictable. Sometimes it
will jump to the non-sensitive page when one keystroke is hit, other
times it will take anywhere from a few seconds to about 80% of the
whole time-out time, but it seems to never go back to 100% of the
time-out time. Bob, is it necessary to somehow reset the timer when
the user goes back to the working page?
 
R

RJQMAN

Bob Phillips - are you still out there?? I would really appreciate
your help in (hopefully) clearing the last hurdle in this quest...
 
R

RJQMAN

Bob Phillips - I have been working with this for days, and I understand
a little more of what is happening, but not all. I am learning by
trial and error - I just keep trying things and then observe the
effect. At this point I can get everything to work EXCEPT that I
cannot figure out any way to get the system to 'reset' and restart the
counter once some activity has taken place on the worksheet. Instead,
the counter still continues, causing the specified activity to happen
as though there had been no activity on the worksheet. However, the
wierd thing is that the new activity on the worksheet seems to start up
a second counter. The specified activity will happen 5 minutes after
the worksheet was first modified, and it will happen 5 minutes after
the worksheet is modified a second time, if the modification takes
place during the original 5 minutes - the first counter is not
'canceled'. Help!
 
Top