Have a message box repeat on timer

[scaffdog845]

Hey gang. I have a file shared amongst my organization that I need to access at a certain time to update. The file has often been left open and unattended on users desktops. I've inserted the following code under the THIS WORKBOOK module to give the users a prompt whne opening the workbook to exit in a timely fashion.

Please Login or Register  to view this content.

Is there a way to get this message to reappear at a certain time interval like every 5 minutes the file is open?

[Logit]

.
You would be better served to include a macro that auto-saves the file at X minutes after opening and auto closes.

[HumphreyDog]

There's a solution here:

stackoverflow.com/questions/22772898/how-to-have-vba-execute-every-10-minutes

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[scaffdog845]

I like that idea. Within our facility there are three users that need wide open access to this file for editing purposes and can not be time limited to how long they have ownership of the file. There are several hundred other users that need to open the file to simply view scheduled work. From an editor's standpoint the issue comes when a "Viewer" opens a file and walks away from the computer for hours with it open. I can always get IT to kick them out of the file but it's a daily pain in the aught.

[underscoreBadger]

As you have a usage distinction between editors and viewers, you could save the file with a password to modify - that way it opens as read-only for your viewers and guarantees that your editors will have the file available to edit at any time.

You would need to balance an approach like this with the need to keep the data current (if a viewer has the file open as read-only, they won't see any edits made until they open it again). Depending on the design of the file, you may be able to separate the data that will be updated into a table object, then have two copies of the file, one that you edit, and one that links to the edited file (and is accessed by your viewers) through that table object. You can set the connection to auto-refresh every few minutes, so the viewers see whatever is the latest save in your master file, and can have the file open as much as they want without impacting other users or running the risk of out of date data.

I know this is the VBA forum, I just want to highlight a potential out-of-the-box solution too.

[Logit]

.
Based on your last post ... I wonder if it would be better for each individual to have a copy of the Master File on their computer. They can do anything they want with their own copy.
Then have the original Master File on the server periodically 'pole' each user's copy to obtain updates and provide each user with the updated file ?

That's a rather convoluted approach to the solution but it does allow everyone to do what they want and not interfere with others.


Overall your situation is .. to put it bluntly ... a mess. A few individuals need unfettered access with their file access ongoing. Others only need to access the file periodically and are
contributing to the issue.

Bottom line ... someone needs to effect disciplinary action on the violators or ... simply ignore the issue and tell everyone when they can play in the sand box nicely their problem will
go away.

[scaffdog845]

Logit. It a posted schedule for a large industrial complex. Everyone needs to have access to it to look at the workers coming to their areas for the day, but there are only three of us that are suppose to edit the file. The issue is people opening it up then leaving it open for hours while they are away from there desk. Then when an editor goes to use it, the file is locked. Current solution is to high IT release the file.

[nigelog]

Similar issue here a few years back so I added code to close workbook after a duration. Ill see can I find workbook with code that may help

[nigelog]

You can display a message or close the workbook. Set to 15 seconds

[Logit]

I suggest adding a macro that checks for lack of any activity. Say after 3 minutes or so, it auto closes the file. Let me know if you are interested in the code.

[scaffdog845]

Thanks for the file Nigelog. I'm not very well versed in VBA. The file produces several Runtime errors on opening.

[scaffdog845]

Logit I think your suggested solution to auto save and close on a certain period of inactivity would be helpful. Thanks, I would be interested in that code..

[Logit]

.
Here is part of the code:

Please Login or Register  to view this content.

Review the entire workbook for all the code.

[scaffdog845]

Sorry it took me so long to get back in here and see this Logit. I follow the logic and it makes sense however it generates a VB error '1004' Method 'OnTime' of object'_Application' failed on the line

Please Login or Register  to view this content.