Having a timer start upon open of workbook and close after 60 minutes

[jwills7]

All, I am trying to deploy a timed excel case study for interviewing purposes but am having trouble thinking of a creative way to enforce time without just sending the worksheet when they need to start and asking them to send it back when they are done.

Is it possible to do the following?
1. Create a sheet in the workbook that says the tabs will be revealed upon enabling macros
2. When macros are enabled, a 60 minute timer begins
3. When 5 minutes are left, the workbook warns the user time is running out
4. When 60 minutes is reached, the file gets saved and closed

Any help would be greatly appreciated, I cannot seem to find a solution for this anywhere.

 

[Herakles]

Put this code into the ThisWorkbook Module.

Private Sub Workbook_Open()
    
    dteInterval = TimeSerial(0, 55, 00)
    Application.OnTime Now + dteInterval, "subWarning"
    
    dteInterval = TimeSerial(0, 60, 00)
    Application.OnTime Now + dteInterval, "subTimeUp"
    
End Sub

You may want to reduce the time values in the lines assigning values to the dteInterval
variable for testing purposes.

And this code into a Code Module.

Public dteInterval As Date

Public Sub subWarning()
    MsgBox "5 minutes to go.", vbOKOnly, "Warning!"
End Sub

Public Sub subTimeUp()
    MsgBox "Time is up!!", vbOKOnly, "Warning!"
End Sub

 

[HaHoBe]

Hi Herakles,

any user in Edit-mode for a cell or entering data into a cell will be notified?

Holger

 

[Herakles]

I think that I know what you are meaning.

A macro will not run whilst the user is in Edit Mode.

The user will have to press Enter or Esc to get out of Edit Mode and then the macro will resume but that
will not meet your objective.

You idealy want to stop the user editing when the time is up.

There may be a way that will work but I'll test it before I post anything.

 

[HaHoBe]

Hi Herakles,

if you don't think about all situation anything like this it might happen, if you care to avoid all odd situations they won't show up. And to be honest: my first thoughts on solving this were pretty much what you have posted. Until I came up with that thought. On thinking about it for me it would need to protect all worksheets (and the structure of the workbook), make the workbook only be used with activated macros and let the users interact only via UserForm. And my final thought was pretty simple: use a Database for this instead of a notoriously uncertain Application like Excel.

Before you take any time OP should clarify if users may only choose from a couple of answers (maybe something like Data/Validation or OptionButtons/ControlButtons(ListBox) or if they are free to enter their answers into the cells.

Just my 2cents on this

Holger

 

[jwills7]

Free to answer on any cells. I will use a database as suggested, thought maybe VBA could do the trick!

 

[HaHoBe]

Hi jwills7,

another remark. Will you limit the total time to be spend on working on the file on 60 minutes (meaning that it will not be available to the user after that time span) or do you allow any user to have 60 minutes on any start of the file? This means: will you save the time spent on the workbook and reduce the available time, what will happen with the data entered when a user chooses to cancel (user might be called away from the screen for some reason)?

VBA could do a lot on this, and usually it's good enough for the casual/normal Exceluser not to get information you want to hide from them. But ...

Holger

 

[jwills7]

Hi jwills7,

another remark. Will you limit the total time to be spend on working on the file on 60 minutes (meaning that it will not be available to the user after that time span) or do you allow any user to have 60 minutes on any start of the file? This means: will you save the time spent on the workbook and reduce the available time, what will happen with the data entered when a user chooses to cancel (user might be called away from the screen for some reason)?

VBA could do a lot on this, and usually it's good enough for the casual/normal Exceluser not to get information you want to hide from them. But ...

Holger

Essentially, I would like to be able to send a potential an employee a workbook, they can open it and begin at any time they would like, but once it is opened, they only have 60 minutes to complete the file before it is saved and locked down again. I am open to any ideas (even an online platform that can perform this duty) as my HR team has to send out 3-4 of these a week and it is quite a pain to manage.

 

[Herakles]

If you use Excel you could use a single or multiple Userforms that derive their content and options for interaction from
data contained in a worksheet. Can you give us some idea of what that content and interaction options would be?