Message displayed after automatically closing down a workbook

[boboivan]

Hi everybody!

For automatically closing down a workbook for 2 minutes of inactivity, I use these codes:

For the common module:

Please Login or Register  to view this content.

And for ThisWorkbook:

Please Login or Register  to view this content.

After closing down, it still remains a blank window of the excel, where I'd like to pop-up a message box for the users to let them know that the workbook got closed (e.g. "File closed for 2 minutes of inactivity")

Please, can someone help me with this?

Thanks!

[LJMetzger]

Try

Please Login or Register  to view this content.

Lewis

[boboivan]

Hi Lewis!
I need to have a message displayed after closing down.

[john55]

I think it's not possible once the wkb is closed...

[boboivan]

@John55

I'm thinking that if it still remains a blank window of the excel, it could be possible to popup a message though.

[slx]

Your vba is in the workbook. If you close the workbook, there is no vba. You need to use an external application. The task scheduler can create popups, use that before closing your workbook.

You would have vba write the scheduled tasked in commandline and run it before closing.

[boboivan]

@Slx
Thank you for your detailed answer. Makes sense everything you said.

Unfortunately I'm not very good with VBA. Is there anything you can do improve the existing codes?

Thx in advance!

[slx]

If you want to learn how to do more with vba, like making an external pop up. I would suggest you give it a try, use command line and what you want to input is the scheduled task.

Command line -> http://social.technet.microsoft.com/...ba?forum=excel
Scheduled task -> http://technet.microsoft.com/en-us/l...px#BKMK_create


Alternatively, you could just use msgbox and application.quit afterwards. This way the person will see the message and when they exit the message box the system will goto the next step which is close. Close as smoothly as possible, you would save, then msgbox and close with no save.

[LJMetzger]

Sorry, Lewis can't read. I missed the part about the pop-up window.
I believe john55 is correct. Once the file is closed, ALL MACRO activity associated with the file stops.


You may be able to do what you want using 'wscript' built into many windows systems. The following code excerpts worked with Excel 2003 and a Vista 64 bit system.

Create a file called PopUpMessage.vbs

Please Login or Register  to view this content.

Termination code excerpt (tested and working):

Please Login or Register  to view this content.

Lewis

[boboivan]

If you want to learn how to do more with vba, like making an external pop up. I would suggest you give it a try, use command line and what you want to input is the scheduled task.

Command line -> http://social.technet.microsoft.com/...ba?forum=excel
Scheduled task -> http://technet.microsoft.com/en-us/l...px#BKMK_create


Alternatively, you could just use msgbox and application.quit afterwards. This way the person will see the message and when they exit the message box the system will goto the next step which is close. Close as smoothly as possible, you would save, then msgbox and close with no save.

Thank you so much Slx!

[boboivan]

Sorry, Lewis can't read. I missed the part about the pop-up window.
I believe john55 is correct. Once the file is closed, ALL MACRO activity associated with the file stops.


You may be able to do what you want using 'wscript' built into many windows systems. The following code excerpts worked with Excel 2003 and a Vista 64 bit system.

Create a file called PopUpMessage.vbs

Please Login or Register  to view this content.

Termination code excerpt (tested and working):

Please Login or Register  to view this content.

Lewis

Thank you so much Lewis!
Works great!

[LJMetzger]

Here's a similar solution using Windows API GetCursorPos() to determine inactivity. I was unable to find an API 'Mouse Move Event' so instead a timer is used to poll the cursor position every few seconds. When a timeout occurs, the file is saved, a wscript message is displayed, and Excel is terminated just like in the previous post.

The following items at the top of 'ModCursorMovement' can be changed to fine tune 'Lack of Cursor Movement Detection':
a. Conditional Compilation Constant 'NEED_TO_DEBUG' - Set 'True' for PRODUCTION and set to 'False' for SOFTWARE DEVELOPMENT.
b. Const 'jDeltaMaxForNoMOVEMENT' to determine 'cursor position sensitivity (e.g. 5 means Delta of 5 pixels means position DID NOT MOVE).
c. Const 'jMaxSecondsOfInactivityALLOWED' to determine TIMEOUT VALUE (e.g. 120 seconds is 2 minutes).
d. Const 'jSecondsBetweenSAMPLES' to determine how often the cursor position is sampled.

When in SOFTWARE DEVELOPMENT mode the debugger 'Immediate Window' (CTRL G) contains POSITION data.

Code follows and is included in the attached file.

Lewis

ThisWorkbook Module:

Please Login or Register  to view this content.

Ordinary Module ModCursorMovement:

Please Login or Register  to view this content.

Ordinary Code modeule LjmTimers:

Please Login or Register  to view this content.

[boboivan]

Thank you for the codes and for your kind support!

Everything works just fine, but if I have a user that doesn't use the mouse but only the keyboard, the workbook closes down.
Anything that can be done with this problem? I'm thinking of a mixture between the original codes I'm using now (see my first post) and these ones .

[LJMetzger]

Hi,

I incorporated Workbook events similar to your original post to reset the Inactivity counter when a keystroke is entered that moves the focus to a different cell. I tried to trap every keystroke, but the solution I tried did not seem reliable.

I also added the ability to open the worksheet to a specific 'Sheet' and 'Cell'. This capability is enabled
ONLY by 'Automatic Save' at this time.

Since I can't trap individual keystrokes, the following items are known not to be detected:
a. Excel Menu item keystrokes
b. Add a sheet
c. Rename a sheet

Please let me know if I have overlooked anything. This worksheet is similar to the previous file. The default timeout is currently set to 10 seconds.

Lewis