Force Macros Enabled And Close When Idle

[ENNIN12]

Hi,


I have a spreadsheet that is shared across the organization and have been running into people locking the spreadsheet and leaving their desks for an extended period of time. I am in need of a VBA code that will first require them to enable macro to be able to use the file, and once they are in the file and inactive in excel for 2 mins, I need excel to save and close the file. Any help will be greatly appreciated.

[SamT]

My office computer is down, so I can't offer much help.

You will need to write a sub for each Workbook Event each of which triggers a Timer or OnTime sub. I think the OnTime is easier

These are algorithms. They are NOT code. They WONT run. But...
If you can understand what they indicate doing, you should be able to write some code that works

Sub AllWorkbookEvents( As needed)
TimerOrOnTime Now
End Sub

SubTimerOrOnTime(StartTime As Long)
Const MaxIdleTimeAllowed As Long = Max Time As DateSerial 'Here for easy modification of idle time. 
'One DateSerial minute = 1/(24*60)
Static EndTime As Long
Static Running As Boolean 'Only set true after running once.

If Running Then 'If not Running, there is no OnTimer to reset
Turn off OnTimer (EndTime) 'must use the same time value as when initiated
End If

 Running =True 
EndTime = StartTime + MaxIdleTime
Create new OnTimer (EndTime)

The thing I've called OnTimer is what saves and closes the book

[Bob Phillips]

I wouldn't think you need all workbook events, it would seem to me you could get away with setting OnTime in the workbook_Open event, and then reset it on a Workbook_SheetChange event. That way, if left idle for any period, you could force a shutdown.

Also, you cannot force macro enabling, that would be a pretty big security hole if it were possible, but the general approach is to have a sheet with a message saying that macros must be enabled to run this workbook. Hide all other sheets except this sheet (and do this in the beforeclose event and save it). On the workbook open event, unhide all other sheets and hide this one. This way, if they enable macros, the workbook is availabel to them, if they don't they just see this message.

[SamT]

XLD, I thought of warning the user that the book is going to close, JIC s/he is actually working, but not actually entering data at the moment.

Is it possible to close a MsgBox programatically and how would you do that?

[Jan Karel Pieterse]

You would need a userform which is scheduled to close. Some air code:

Option Explicit
Dim mfForm As UserForm1
Dim mdNextTime as Double

Sub ShowMessage()
    Set mfForm = New Userform1
    mdNextTime = Now() + TimeValue("00:00:05")
    Application.Ontime mdNextTime, "CloseForm"
    mfForm.Show vbModeless
End Sub

Sub CloseForm()
    If not mfForm Is Nothing Then
        mfForm.Hide
        Unload mfForm
        Set mfForm = Nothing
    End If
End Sub

[Kenneth Hobs]

Welcome to the forum ENNIN12!

What you ask can be fairly involved. It consists of two separate issues. The first is fairly easy. See these posts for examples of forced macro enabling.
'Ken Puls, http://www.vbaexpress.com/kb/getarticle.php?kb_id=379
' To protect by username see: http://www.vbaexpress.com/forum/showthread.php?t=42730

For the 2nd issue, you might be able to achieve that by adding a routine to each of the monitored worksheet's Change event. You would reset Application.OnTime().

Even if the time were exceeded by OnTime, I would give the user one more chance and display a MsgBox or UserForm with a wait time just in case they still don't respond. A response would reset your OnTime timer.

e.g.
http://www.xldynamic.com/source/xld.xlFAQ0022.html

[SamT]

IMO, it is important to consider these Users as Very Special, and give them every possible way to kill the OnTime.

That is one reason I originally suggested every Event.

Also, when the Warning pops up, I would try to kill it with every Event, even KeyDown and MouseMove.

My goal would be to make "telling the Macro" that the User is still there as easy and varied as programatically possible. Even to the point of using an API to trap Windows (OS) Events and using a two stage timer system where the Excel Event trapping is turned off by the next non-Excel event and the OnTime is set to a longer period to allow the User time to respond to required actions outside Excel.

If there are no OS events in the Excel OnTime period, Then Close the Workbook, Else, IF there are no Excel Events in the OS allowed time, then Close.