Auto Close WorkBook

[New2ExcelVBA87]

Hey Everybody,

Not sure if this is already a thread as I've tried searching for answers but come up with only half solutions to what I needed, or incomplete code. I've tried a smattering of code I could find in the forums, and even tried combining bits and pieces, but to no avail. This is mostly due to my lack of understanding in VBA. Any assistance will be greatly appreciated. I'll provide what I'm trying to accomplish below, and I'll provide the code I have.

I will mention that I did find code that did work after I tweaked, and modified it a bit, but I don't understand how it works. I'm attempting to work off of code that I have some understanding of, and this is why I'm reaching out.

I'm trying to auto close out of workbooks for the reports I update as there are users who have them open, and forget to close them at night. I've set the auto close time to 12am.

Below is the code that does not work, but to my understanding/at first glance should work. Any insight would be greatly appreciated.

Private Sub Workbook_Open()


Static SchedSave

If SchedSave <> 0 Then
Application.OnTime SchedSave, "SaveWork", , False
End If
SchedSave = TimeValue("09:29:00") ' Insert Desired time in Military
Application.OnTime SchedSave, "SaveWork", , True


End Sub


Sub SaveWork()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub


Below is the code that does work, but I don't understand it, and I will most definitely utilize it until I find a different solution. However I feel like this code is a bit overly complicated? vs. possibly creating code that can just be in one place (ThisWorkbook) instead of being in "ThisWorkbook" and Modules. Any insights would be greatly appreciated.


' Insert into ThisWorkbook


Private Sub Workbook_Open()
Reset
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Reset
End Sub


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




' Insert into Module
Sub Reset()
Static SchedSave
If SchedSave <> 0 Then
Application.OnTime SchedSave, "SaveWork", , False
End If
SchedSave = TimeValue("12:00:00") ' 12AM
Application.OnTime SchedSave, "SaveWork", , True
End Sub


Sub SaveWork()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

 

[JoeMo]

Welcome to the board.

The time at which your SaveWork procedure is to be run must be stored in a public variable. In the code that works this is done in the Reset subroutine which is in a standard module using the variable SchedSave. In the code that doesn't work for you, that same variable is in a Thisworkbook module which is not public.

 

[JLGWhiz]

Could be where you install the code:
This would go in the ThisWorkbook code module:

Private Sub Workbook_Open()
Static SchedSave
If SchedSave <> 0 Then
Application.OnTime SchedSave, "SaveWork", , False
End If
SchedSave = TimeValue("09:29:00") ' Insert Desired time in Military
Application.OnTime SchedSave, "SaveWork", , True
End Sub

This would go in the standard code module:

Sub SaveWork()
ThisWorkbook.Save
ThisWorkbook.Close
End

the only difference between the ones that do work and the ones that don't, is where the code resides and when the different macros are called. It boils down to getting the SaveWork macro to run.

 

[New2ExcelVBA87]

Welcome to the board.

The time at which your SaveWork procedure is to be run must be stored in a public variable. In the code that works this is done in the Reset subroutine which is in a standard module using the variable SchedSave. In the code that doesn't work for you, that same variable is in a Thisworkbook module which is not public.

Hey Joe,

Thanks for the super fast response. With my current level of VBA understanding I only understood the first sentence of your answer. I'm super sorry about that! I know it's sad that I'm asking for help when I only comprehend so little of what I'm trying to ask =[ However I'm most definitely willing to learn. In regards to the issue at hand, is there a macro that can be written where the VBA code can all be under ThisWorkbook? Or am I over simplifying, and need a higher level of understanding of VBA in order to write code to auto close out of the workbooks?

Thanks again,

 

[New2ExcelVBA87]

Hey JLG,

I put your suggestion into practice, and it totally worked. It looks like this is pretty much what Joe was saying, and I appreciate the help the both of you gave :] The only things is I don't understand why

Sub SaveWork()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

had to be stored in a public variable. My background in VBA is basically creating macro's to do repetitive tasks, and I have some SQL knowledge as well (more so than VBA). I don't have a big dev background, and I'm a report analyst.

Thanks,

Could be where you install the code:
This would go in the ThisWorkbook code module:

Private Sub Workbook_Open()
Static SchedSave
If SchedSave <> 0 Then
Application.OnTime SchedSave, "SaveWork", , False
End If
SchedSave = TimeValue("09:29:00") ' Insert Desired time in Military
Application.OnTime SchedSave, "SaveWork", , True
End Sub

This would go in the standard code module:

Sub SaveWork()
ThisWorkbook.Save
ThisWorkbook.Close
End

the only difference between the ones that do work and the ones that don't, is where the code resides and when the different macros are called. It boils down to getting the SaveWork macro to run.

 

[JoeMo]

Hey Joe,

Thanks for the super fast response. With my current level of VBA understanding I only understood the first sentence of your answer. I'm super sorry about that! I know it's sad that I'm asking for help when I only comprehend so little of what I'm trying to ask =[ However I'm most definitely willing to learn. In regards to the issue at hand, is there a macro that can be written where the VBA code can all be under ThisWorkbook? Or am I over simplifying, and need a higher level of understanding of VBA in order to write code to auto close out of the workbooks?

Thanks again,

You are welcome.

The short answer is that the SaveWork procedure cannot be in a Thisworkbook module. So, no matter how you code it, the OnTime method cannot be used in a way that puts all the code required to accomplish your task into a Thisworkbook module.

 

[New2ExcelVBA87]

PS.

So now that I know that the first macro and 2nd work I'm wondering which is more efficient and clean, and why?

1ST MACRO
' Insert into ThisWorkbook
Private Sub Workbook_Open()


Static SchedSave

If SchedSave <> 0 Then
Application.OnTime SchedSave, "SaveWork", , False
End If
SchedSave = TimeValue("09:29:00") ' Insert Desired time in Military
Application.OnTime SchedSave, "SaveWork", , True


End Sub




' Insert into module
Sub SaveWork()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

2ND MACRO

' Insert into ThisWorkbook


Private Sub Workbook_Open()
Reset
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Reset
End Sub


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




' Insert into Module
Sub Reset()
Static SchedSave
If SchedSave <> 0 Then
Application.OnTime SchedSave, "SaveWork", , False
End If
SchedSave = TimeValue("12:00:00") ' 12AM
Application.OnTime SchedSave, "SaveWork", , True
End Sub


Sub SaveWork()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

 

[New2ExcelVBA87]

Hey Joe,

Thanks for the answer :] Super appreciated.

You are welcome.

The short answer is that the SaveWork procedure cannot be in a Thisworkbook module. So, no matter how you code it, the OnTime method cannot be used in a way that puts all the code required to accomplish your task into a Thisworkbook module.

 

[New2ExcelVBA87]

I've also posted this on another board. Link provided below.

Auto Close WorkBook

 

[JLGWhiz]

Hey JLG,

I put your suggestion into practice, and it totally worked. It looks like this is pretty much what Joe was saying, and I appreciate the help the both of you gave :] The only things is I don't understand why

Sub SaveWork()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

had to be stored in a public variable. My background in VBA is basically creating macro's to do repetitive tasks, and I have some SQL knowledge as well (more so than VBA). I don't have a big dev background, and I'm a report analyst.

Thanks,

Happy to help,
Regards, JLG

P.S. Code that begins with Private in the title line should only be run from ThisWorkbook, Sheet or UserForm code modules depending on the event for which they were written. Those macros without the Private keyword would normally be put into one of the public code modules, which are numbered beginning with 1.