Help! Where does this code go?

[zakasnak]

Do I put this in the worksheet tab "View Code"? That's where it would make sense to me....

I can't tell that it's working, tho.

Private Sub Workbook_Open()
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes
Application.DisplayAlerts = True
TimeInMinutes = 30 'Timer is set for 180 minutes; change as needed.
If TimeInMinutes > 5 Then
TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60)
    Start = Timer
    Do While Timer < Start + TotalTimeInMinutes
        DoEvents
    <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place w:st="on">Loop</st1:place>
    Finish = Timer
    TotalTime = Finish - Start
    Application.DisplayAlerts = False
    MsgBox "This file has been open for " & TotalTime / 60 & " minutes. You have 5 minutes to save before Excel closes."
End If
<o:p> </o:p>
Start = Timer
    Do While Timer < Start + (5 * 60)
        DoEvents
    <st1:place w:st="on">Loop</st1:place>
    Finish = Timer
    TotalTime = Finish - Start
    Application.DisplayAlerts = False
    MsgBox "Excel will now close."
    Application.Quit
End Sub

 

[VoG]

Press ALT + F11 to open the VBE, in the Project window double click ThisWorkbook. Paste in the code]

 

[Jonmo1]

That will go in the module named ThisWorkbook

 

[zakasnak]

Thanks fellas! I appreciate all the help!

 

[zakasnak]

Thanks fellas! I appreciate all the help!

I set the timer for 10m to try it, but it doesn't appear to be working on a test workbook... any ideas?

another question.... Does this close Excel or just the spreadsheet running the timer? The last message box says that "Excel will close".

 

[Ruddles]

It starts running when the workbook opens, so just pasting it into ThisWorkbook isn't enough: you have to save the workbook, close it, then re-open it again.

 

[VoG]

And it does close Excel, not just the workbook.

 

[zakasnak]

It starts running when the workbook opens, so just pasting it into ThisWorkbook isn't enough: you have to save the workbook, close it, then re-open it again.

I've done this... but no timer ever pops up & no message boxes & Excel doesn't close.

I've even closed Excel & reopened the file.... nothing.

 

[zakasnak]

And it does close Excel, not just the workbook.

Is there a way to modify so that only the open workbook (with the timer) closes?

 

[VoG]

Replace

Application.Quit

with

ActiveWorkbook.Close


If you look at the code it manipulates TimeInMinutes to get total time. Try replacing

TimeInMinutes=30

with

TimeInMinutes=6

It should then give you a message in about 36 minutes from opening the workbook.