OnTime Function

[Guest]

What I'm trying to do is save and close a spreadsheet after a certain amount of time has passed. The code I wrote below is very simple and probably not the best, but I'm a rookie so that's my excuse. I'm having a couple of problems with what I've written. First, is it possible to save a file with it's current name instead of making another filename? Basically using "Save" instead of "Save As". Second, I have a vbYesNo message box, but I want to save and shut the file down if the user doesn't answer the msgbox within 2 minutes. Last but not least and kind of part of the first question, I want to suppress all messages about saving the file with the same name

Any help or other ideas would be appreciated. I'm doing this so I can put an end to my co-workers bickering over who has a spreadsheet open and why they left their desk for an hour....

K

by the way -- the 15 seconds is just being used while I'm testing this, the normal interval will be 20 minutes

Sub timertest(
Application.OnTime Now + TimeValue("00:00:15"), "timermsg
End Su

Sub timermsg(
Dim closeme As Intege
Dim thankyou As Intege

closeme = MsgBox("Continue Working on Spreadsheet?", vbYesNo, "Workbook Timer Expired"
If closeme = vbYes The
thankyou = MsgBox("Thank You", vbOKOnly, "Continue Work"
End I

If closeme = vbNo The
Application.SaveWorkspace "G:\Special Projects\NO-MO Rehome\St Joe - Maryville DS1 Tracker - 032404-1.xls
Application.ActiveWorkbook.Clos
End I

End Sub

 

[Bob Phillips]

Kevin,

Point 1.

Activeworkbook.Save

Point 2.

You can't do it with MsgBox, but you can use Windows Scripting. Here is an
example,

Dim nRes As Long
Dim oWSH As Object

Set oWSH = CreateObject("WScript.Shell")
nRes = oWSH.Popup(Text:="Click a button, or just wait ", _
SecondsToWait:=120, _
title:="Automatic MsgBox Timeout", _
Type:=vbYesNoCancel)


The msgbox will timeout after 2 minutes. If the user clicks a button on the
box, it returns vbYes or vbNo, whereas if the msgbox times out, it
returns -1.

Point 3.

If you use Save, it is irrelevant, you won't get a message.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

What I'm trying to do is save and close a spreadsheet after a certain

amount of time has passed. The code I wrote below is very simple and
probably not the best, but I'm a rookie so that's my excuse. I'm having a
couple of problems with what I've written. First, is it possible to save a
file with it's current name instead of making another filename? Basically
using "Save" instead of "Save As". Second, I have a vbYesNo message box,
but I want to save and shut the file down if the user doesn't answer the
msgbox within 2 minutes. Last but not least and kind of part of the first
question, I want to suppress all messages about saving the file with the
same name.

Any help or other ideas would be appreciated. I'm doing this so I can

put an end to my co-workers bickering over who has a spreadsheet open and
why they left their desk for an hour.....

KG

by the way -- the 15 seconds is just being used while I'm testing this,

the normal interval will be 20 minutes.

Sub timertest()
Application.OnTime Now + TimeValue("00:00:15"), "timermsg"
End Sub

Sub timermsg()
Dim closeme As Integer
Dim thankyou As Integer

closeme = MsgBox("Continue Working on Spreadsheet?", vbYesNo, "Workbook Timer Expired")
If closeme = vbYes Then
thankyou = MsgBox("Thank You", vbOKOnly, "Continue Work")
End If

If closeme = vbNo Then
Application.SaveWorkspace "G:\Special Projects\NO-MO Rehome\St

Joe - Maryville DS1 Tracker - 032404-1.xls"