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

 

[zakasnak]

Tushar's code is undoubtedly better ...

Would I put it in the same place?

[FONT=Trebuchet MS]Option Explicit<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT]
[FONT=Trebuchet MS]Dim WithEvents CountdownTimer As TMTimer.clsTimer<o:p></o:p>[/FONT]
<o:p></o:p>
[FONT=Trebuchet MS]Private Sub CountdownTimer_CountdownComplete()<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   Unload Me<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   End Sub<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]Private Sub CountdownTimer_TimerTick(TimerVal As Single)<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   Me.Label1.Caption = "This form will close in " _<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]       & Round(CountdownTimer.CurrentMilliSecsLeft / 1000, 0) & " seconds"<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   End Sub<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]Private Sub startCounter()<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   Set CountdownTimer = TMTimer.createTimer<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   With CountdownTimer<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   .CountdownDurationMilliSecs = 10 * 1000<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   .TickIntervalMilliSecs = 3 * 1000<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   .TimerType = .TimerTypeCountdown<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   .startTimer<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]       End With<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   End SubPrivate Sub Cancel_Click()<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   CountdownTimer.cancelTimer<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   Unload Me<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   End Sub<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]Private Sub OK_Click()<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   MsgBox "OK"<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   CountdownTimer.cancelTimer<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   Me.Hide<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   End Sub<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]Private Sub Reset_Click()<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   CountdownTimer.resetCountdown<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   End Sub<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]Private Sub UserForm_Activate()<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   startCounter<o:p></o:p>[/FONT]
[FONT=Trebuchet MS]   End Sub[/FONT]

</PRE>





</PRE>

 

[zakasnak]

Your second link. Are you keeping up?

I think so LOL!

I looked at the one : A userform that closes after a predetermined time (unless the user resets the countdown). I think this would work for me, so that if a buyer was in the sheet, they would have the option of resetting the timer.

Do I put the code in a module in ThisWorkbook?

Can you help me with the buttons?

 

[VoG]

No, I was just saying that Tushar's code is better but it involves Class modules and such.


Do you simply want to close a workbook after a specified time. If so you would be better off using the OnTime method.

 

[zakasnak]

.... and where does the option explicit code go?

 

[zakasnak]

No, I was just saying that Tushar's code is better but it involves Class modules and such.


Do you simply want to close a workbook after a specified time. If so you would be better off using the OnTime method.

The one that started the thread....

 

[zakasnak]

I'm not afraid of Class Modules, but I don't know much about them. Can they be saved with the sheet in question, so when another user opens, they need to enable & the code will work on their station?

 

[zakasnak]

So ... I've left this test sheet open for almost an hour & no message box & no closing of Excel...

What am I doing wrong?

I think I figured it out.... I pasted the code into a Module in ThisWorkbook, NOT directly into ThisWorkbook. DOH!

 

[VoG]

OK, let us try a different approach. Replav=ce the code in the ThisWorkbook module with

Private Sub Workbook_Open()
dTime = Now + TimeValue("00:15:00")
Application.OnTime dTime, "MyMacro"
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
if dtime > now then Application.OnTime dTime, "MyMacro", , False
End Sub

Then insert a regular module ()Insert>Module) and paste in

Public dTime As Date
Sub MyMacro()
ActiveWorkbook.Close savechanges:=False
End Sub

Save, close and re-open. The macro should close the sheet after 15 minutes.

 

[zakasnak]

So here's a question. What if I just want the file to close without any warnings after a period of time? ... say 8 hours (this would be after the workday is over).

I overwrite this file every week, so even if there were changes, they would be overwritten with my new file (this is why I need it to close in the first place, because someone has it open & I can't overwrite).

Can I alter the code with a ThisWorkbook.Save &/or Close without any message boxes?

 

[VoG]

See http://www.mrexcel.com/forum/showpost.php?p=2533055&postcount=28