Excel VBA close after time (needs fixed a little)

lxmn1234

New Member
Joined
Jun 12, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
The code I am using works, and closes the workbook except for two cases: 1. There is an active cell. 2. You go to close, but forget to hit the 'Save', 'Don't Save', or 'Cancel' buttons.
Code Below, Can anyone help me out? Thanks.
ThisWorkbook:
VBA Code:
Option Explicit

Private Sub Workbook_Open()
    Call SetTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call StopTimer
End Sub

Module:
VBA Code:
Dim DowntTime As Date

Sub SetTimer()
    DownTime = Now + TimeValue("00:10:10")
   Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", Schedule:=True
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", Schedule:=False
      
      
 End Sub
 
 
Sub ShutDown()
   Application.DisplayAlerts = False
   
   With ThisWorkbook
       .Saved = False
       .Close
    End With
End Sub

Again, thanks!!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
305
Office Version
  1. 365
Platform
  1. Windows
What do you mean by an "active cell"? Do you mean that there is a cell in which you're in the middle of typing something?

Also , when you say it works, what are you expecting it to do? I don't want to try a script that will shut down my excel because that will just cause headaches, but look at the code, it seems to set a timer running from the moment it starts for 10mins and 10 seconds, at which point it runs the Shutdown script. That is, unless you close it, in which case it cancels the Shutdown script.

Then the Shutdown script executes, it turns off alerts, changes the switch in the workbook that indicates its current state has been saved or not to 'off' (though I can't work out why when you've just turned off the alerts), and then it closes the workbook.
 

lxmn1234

New Member
Joined
Jun 12, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Sorry for the late response, I didn't see this in my email.

What do you mean by an "active cell"? Do you mean that there is a cell in which you're in the middle of typing something?
Yes, by "active cell" I mean exactly the same thing you said.

Also , when you say it works, what are you expecting it to do? I would like the code to close the workbook, no matter what is happening. I tried to describe those cases above 1. and 2. that are breaking the code and I don 't know how. I don't want to try a script that will shut down my excel because that will just cause headaches, but look at the code, it seems to set a timer running from the moment it starts for 10mins and 10 seconds, at which point it runs the Shutdown script. That is, unless you close it, in which case it cancels the Shutdown script.

Then the Shutdown script executes, it turns off alerts, changes the switch in the workbook that indicates its current state has been saved or not to 'off' (though I can't work out why when you've just turned off the alerts), and then it closes the workbook.

I believe your interpretation of the script is correct. And I guess we don't need ".Saved = False" portion of the code. I'll do some testing and see what happens without it.
 

lxmn1234

New Member
Joined
Jun 12, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
What do you mean by an "active cell"? Do you mean that there is a cell in which you're in the middle of typing something?

Also , when you say it works, what are you expecting it to do? I don't want to try a script that will shut down my excel because that will just cause headaches, but look at the code, it seems to set a timer running from the moment it starts for 10mins and 10 seconds, at which point it runs the Shutdown script. That is, unless you close it, in which case it cancels the Shutdown script.

Then the Shutdown script executes, it turns off alerts, changes the switch in the workbook that indicates its current state has been saved or not to 'off' (though I can't work out why when you've just turned off the alerts), and then it closes the workbook.

Case 2. is when you go to close, but forget to finish closing by hitting one of those buttons below.
Case 2. Picture.png
 

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
305
Office Version
  1. 365
Platform
  1. Windows
If you're absolutely certain that you want it to close without saving, you could write:

ThisWorkbook.Close False
 

Watch MrExcel Video

Forum statistics

Threads
1,123,358
Messages
5,601,149
Members
414,431
Latest member
JustmemyselfandI

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top