You need code for two areas of your workbook: some for the Workbook_Open()
event and some to deal with events on the sheet (Tab 1) where the employees
enter data.
Here is the code for the Workbook_Open() event. What it does is just force
display of Tab 2 (graph sheet) when the workbook is first opened. This in
turn forces the user to select the data entry sheet to use it, and that
brings the timing code into play. To put is code into the proper place, open
your workbook and right-click on the small Excel icon immediately to the left
of the word File in the menu toolbar and choose View Code from the list that
appears. copy and paste this code into that code module:
In all of this code where I have typed "Sheet2" - change to the name of the
graph sheet (with double-quotes) as "GraphSheet" or whatever it is named.
Private Sub Workbook_Open()
'change Sheet2 to name of sheet
'with your graph on it
'this makes sure that the user
'has to select the data entry
'sheet to start using it, and that
'assures you that the time-out
'process gets in to the act.
Worksheets("Sheet2").Activate
End Sub
The rest of this code goes into the code module for the user-data-entry
sheet. To get to where it needs to go, right-click on the sheet's name tab
and again choose [View Code], cut and paste into it.
Const TimeAllowed = 180 '180 seconds = 3min
Dim StopTime As Long
Private Sub Worksheet_Activate()
StopTime = Timer + TimeAllowed
Do While Timer <= StopTime
DoEvents ' lets you do other things
Loop
'change Sheet2 to the name of
'the sheet with your graph
Worksheets("Sheet2").Select
End Sub
Private Sub Worksheet_Change(ByVal _
Target As Range)
StopTime = Timer + TimeAllowed
End Sub
Private Sub Worksheet_SelectionChange(ByVal _
Target As Range)
StopTime = Timer + TimeAllowed ' restart clock
End Sub
How it works - when you select/activate the data entry sheet, the
_Activate() code kicks in and just starts looping forever until 180 seconds
have passed ... unless the clock is "reset". The DoEvents allows you to
continue to work within the workbook and even other applications.
The _Change() and _SelectionChange() events restart the clock if anyone does
anything on the worksheets that has to do with data entry, so each time they
choose another cell, or enter data into a cell, they get another 3 minutes on
the clock.
Hope this helps.
traveye said:
I have an excel spreadsheet that a lot of employees use to enter data on tab
1. Tab 2 is used to graph the data. Is there any way to give a time limit to
default to tab 2?
Example: employee finshes entering data in tab 1 and goes back to work. 3
minutes after sitting idle the spreadsheet automatically switches back to tab
2.
I know this is a long shot but i thought i would throw it out there!
