Automatically close workbook after a period of seconds

P

pells

Active Member
Joined
Dec 5, 2008
Messages
361
It is possible to automatically close a workbook on open of Excel after, say 3 seconds?

Thanks.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Code: 
Private Sub Workbook_Open() 
Application.OnTime Now + TimeValue("00:00:03"), "Close_code"
[B]End Sub[/B]
 
sub Close_code()
thisworkbook.close savechanges:= true
msgbox "closing"
 
Last edited:
Upvote 0
pedie said:
Code: 
Private Sub Workbook_Open() 
Application.OnTime TimeValue("00:00:03"), "Close_code"
[B]End Sub[/B]
 
sub Close_code()
thisworkbook.close savechanges:= true
msgbox "closing"
Click to expand...
Perfect, many thanks Pedie.
 
Upvote 0
Hi there,

Try:

Code: 
Private Sub Workbook_Open()
    Application.OnTime Now + TimeValue("00:00:03"), "closeWB"
End Sub
Sub closeWB()
    ActiveWorkbook.Close
End Sub
 
Upvote 0
Hi,

You might want to add an argument to the close; see the VBA helpfiles.
Code: 
Sub closeWB()
    ActiveWorkbook.Close True
End Sub
If a user makes changes, you will get a "do you want to save changes" popup.

kind regards,
Erik
 
Upvote 0
erik.van.geit said:
Hi,

You might want to add an argument to the close; see the VBA helpfiles.
Code: 
Sub closeWB()
    ActiveWorkbook.Close True
End Sub
If a user makes changes, you will get a "do you want to save changes" popup.

kind regards,
Erik
Click to expand...
Many thanks for this great suggestion.

Do you also know if its possible to autosave an excel workbook on close on Excel? If so, what would be the code and where would it go?

Once again, many thanks.
 
Upvote 0
Try:

Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWorkbook.Save
End Sub
 
Upvote 0
Hi,

Forgot to tell you (didn't need this autoclose stuff for a long time):

PROBLEM
If the user closes the workbook before your procedure runs, the workbook will reopen to run it. You need to cancel the procedure: see the item "OnTime" (argument "schedule") in the helpfiles.

SOLUTION
In the workbookmodule
Code: 
Option Explicit
 
Private Sub Workbook_Open()
SetCloseTime
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
CancelSchedule
End Sub

In a normal module
Code: 
Option Explicit
 
Private when As Variant
 
Sub closeWB()
ActiveWorkbook.Close True
End Sub
 
Sub SetCloseTime()
when = Now() + TimeValue("00:00:05")
Application.OnTime EarliestTime:=when, Procedure:="closeWB"
End Sub
 
Sub CancelSchedule()
On Error Resume Next
Application.OnTime EarliestTime:=when, Procedure:="closeWB", schedule:=False
On Error GoTo 0
End Sub

best regards,
Erik
(won't probably be around for some days after this message)
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question
VBA to automatically save and close an Excel file after a period of inactivity in the file
Replies
1
Views
1K
Logit
Logit
B
  • Solved
Macro to open other Excel files, run a specific Macro, and close?
Replies
2
Views
273
Bret1
B
drom
  • Question
My code line close Excel App
Replies
4
Views
174
drom
drom
B
  • Question
VBA to close workbook after 10minutes of inactivity
Replies
5
Views
582
Logit
Logit
G
  • Question
Open workbooks in folder and run Macro in each book
Replies
7
Views
360
glynn1969
G

Forum statistics

Threads
1,214,911
Messages
6,122,194
Members
449,072
Latest member
DW Draft

Share this page

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
Back
Top