PDA

View Full Version : VBA - Auto Select Sheet, Save & Close after 1 hour



jakmorg
05-25-2016, 07:04 AM
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
05-25-2016, 07:39 AM
Put a dot, (.) in front of "Sheets,"

.Sheets("Home").Selectbecause it is inside the With. . . End With and belongs to ThisWorkbook.

jakmorg
05-25-2016, 07:44 AM
You are a hero, thankyou! I was so close but so far haha, cheers.

jakmorg
05-25-2016, 07:47 AM
Oh wait a sec, it just came up with it again the second time I tried it.

16257

SamT
05-25-2016, 08:07 AM
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
05-25-2016, 08:51 AM
1625816259:(

SamT
05-25-2016, 05:39 PM
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
05-25-2016, 06:40 PM
IMO, the proper steps to writing an OnTime sub is

Perfect the sub that it will be calling. In your case that is CloseWb().
Then add the MsgBox and Exit Sub lines. Now CloseWb() is a test Stub.
Declare the ScheduledTime Variable and DelayTime Constant. Set the DelayTime very short, ie 5 seconds
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().
Perfect the OnTime Sub.
Reset the DelayTime to about 10 seconds.
Write the Cancel OnTime sub. The Cancel OnTime Sub is my last ResetRunTime()
To perfect ReSetRunTime(), run TestOnGoingRunTime() and immediately run ReSetRunTime(). Wait 10 seconds to see if it worked.
After you have the Cancel Ontime sub and the OnTime sub perfected. . .
Write a final CancelOnTime() sub, add the Cancel OnTime code and the If Not IsNull(ScheduledTime) test to it.
Write a Stub to set the ScheduledTime Variable to some time. You can reset it to Null with the Menu:=Debug>>End.
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"