VBA - Auto Select Sheet, Save & Close after 1 hour

[jakmorg]

Hi,

I am trying to write some code to select a certain worksheet, prior to saving and closing the workbook 1 hour after opening.
I have successfully used the below code to auto save-close, although when I add the line regarding selecting the home sheet a debug error comes up. Any help would be greatly appreciated?

This Workbook:

Option Explicit

Private Sub Workbook_Open()
 EndTime = Now + TimeValue("01:00:00")
    RunTime
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
End Sub

Module:

Option Explicit

Public EndTime

Sub RunTime()
    Application.OnTime _
    EarliestTime:=EndTime, _
    Procedure:="CloseWB", _
    Schedule:=True
End Sub
 
Sub CloseWB()
    Application.DisplayAlerts = False
    With ThisWorkbook
         Sheets("Home").Select '<---------
        .Save
        .Saved = True
        .Close
    End With
End Sub

[SamT]

Put a dot, (.) in front of "Sheets,"

.Sheets("Home").Select

because it is inside the With. . . End With and belongs to ThisWorkbook.

[jakmorg]

You are a hero, thankyou! I was so close but so far haha, cheers.

[jakmorg]

Oh wait a sec, it just came up with it again the second time I tried it.

Capture.PNG

[SamT]

Here is a way to wait after each use, rather than a fixed time after opening.

ThisWorkbook: These are the events that a User will trigger during use of the Workbook.

Option Explicit 

Private Sub Workbook_Open()
RunTime
End Sub

Private Sub Workbook_Activate()
RunTime
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
RunTime
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
RunTime
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
RunTime
End Sub

Module:

Dim EndTime As Date

Const WaitTime As String = "01:00:00"

Sub RunTime()

If Not IsNull(EndTime) Then 
    Application.OnTime _
    EarliestTime:=EndTime, _
    Procedure:="CloseWB", _
    Schedule:=False
End If
    
    EndTime = Now + TimeValue(WaitTime)
    
    Application.OnTime _
    EarliestTime:=EndTime, _
    Procedure:="CloseWB", _
    Schedule:=True
End Sub
 
Sub CloseWB()
    Application.DisplayAlerts = False
    With ThisWorkbook
        .Sheets("Home").Select
        .Saved = True
        .Close
    End With
End Sub

[jakmorg]

Capture.jpgCapture2.PNG

[SamT]

My other computer died and I haven't replaced it yet. The computer I am using now is a bare bones system with a minimal Excel on it.

However the code I posted Compiled when I wrote it.

OnTime with Schedule = false cancels the existing OnTime.

Troubleshooting OnTime subs is difficult.

Function ReSetEndTime()
EndTime=Null
End Function

Sub testInitialRuntime()
'Resets EndTime to see if code bypasses If Not IsNull section in RunTime
ResetEndTime
RunTime
End Sub

Sub TestOnGoingRunTime()
'
RunTime
End SubTime

Sub ResetRunTime()
'Cancels OnTime. EndTime must not be Nothing or Null 
'RunTime must have executed OnTime w/Schedule = True
        Application.OnTime _
        EarliestTime:=EndTime, _
        Procedure:="CloseWB", _
        Schedule:=False
End Sub

There is no way to stop an OnTime event that has already been scheduled. I recommend that you Use this CloseWb Sub until you have the code perfected

Sub CloseWB() 
MsgBox"Running CloseWb Sub"
Exit Sub

    Application.DisplayAlerts = False 
    With ThisWorkbook 
        .Sheets("Home").Select 
        .Saved = True 
        .Close 
    End With 
End Sub

And setting the WaitTime Constant to 5 seconds

[SamT]

IMO, the proper steps to writing an OnTime sub is

1. Perfect the sub that it will be calling. In your case that is CloseWb().
2. Then add the MsgBox and Exit Sub lines. Now CloseWb() is a test Stub.
3. Declare the ScheduledTime Variable and DelayTime Constant. Set the DelayTime very short, ie 5 seconds
4. Write a Stub to set the Scheduled time, (TestOnGoingRunTime()) and call the OnTime Sub. In your case the OnTime sub is a very basic version of RunTime().
5. Perfect the OnTime Sub.
6. Reset the DelayTime to about 10 seconds.
7. Write the Cancel OnTime sub. The Cancel OnTime Sub is my last ResetRunTime()
8. To perfect ReSetRunTime(), run TestOnGoingRunTime() and immediately run ReSetRunTime(). Wait 10 seconds to see if it worked.
9. After you have the Cancel Ontime sub and the OnTime sub perfected. . .
10. Write a final CancelOnTime() sub, add the Cancel OnTime code and the If Not IsNull(ScheduledTime) test to it.
11. Write a Stub to set the ScheduledTime Variable to some time. You can reset it to Null with the Menu:=Debug>>End.
12. After you have perfected the Cancel OnTime Code, add it to the OnTime Sub along with a line to reset the Scheduled time.

You should now have a good Ontime sub and a good CancelOnTime sub and the ScheduledTime and DelayTime Vars


Now you can write the ThisWorkbook Event Subs To call the OnTime Sub. The first one to write is the Workbook_BeforeClose() wherein you call the CancelOnTime sub.

BTW, the proper usage in CloseWb is "If Not .Saved Then .Save"