How to close excel workbook after 5 minutes it opened?

ODSCm

New Member
Joined
Dec 18, 2020
Messages
30
Office Version
  1. 2016
Platform
  1. Windows
Hello everyone.

I want our common excel workbook get itself closed automatically after 5 minutes of its opening.
Here if excel workbook is opened by someone read-only it will be still closing but without saving. If it was opened by someone who knows the password then it will be closing itself after 5 minutes by saving.

How should be the macro for that?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,770
Office Version
  1. 365
Platform
  1. Windows
Perhaps in ThisWorkbook module Open event have
VBA Code:
Private Sub workbook_Open()
Application.OnTime Now + TimeValue("00:05:00"), "EventMacro"
End Sub

and in a standard module, have

Public alertTime As Variant '<< this goes at very top, under Option Explicit, assuming you use that.

Then after the prior line have this sub:

VBA Code:
Public Sub EventMacro()

If ThisWorkbook.ReadOnly Then
   ThisWorkbook.Saved = True
   ThisWorkbook.Close
Else
   ThisWorkbook.Close
End If

End Sub
If wb is read only it should just close without saving. If it's not read only, you should get a prompt to save if it was edited. You didn't say what to do about that so I assumed a prompt to save would be safe enough. That way, closing can be cancelled if needed. However, the event won't run again if cancelled. The "00:05:00" is 5 minutes & code should run 5 mins after wb is opened.
 

ODSCm

New Member
Joined
Dec 18, 2020
Messages
30
Office Version
  1. 2016
Platform
  1. Windows
Perhaps in ThisWorkbook module Open event have
VBA Code:
Private Sub workbook_Open()
Application.OnTime Now + TimeValue("00:05:00"), "EventMacro"
End Sub

and in a standard module, have

Public alertTime As Variant '<< this goes at very top, under Option Explicit, assuming you use that.

Then after the prior line have this sub:

VBA Code:
Public Sub EventMacro()

If ThisWorkbook.ReadOnly Then
   ThisWorkbook.Saved = True
   ThisWorkbook.Close
Else
   ThisWorkbook.Close
End If

End Sub
If wb is read only it should just close without saving. If it's not read only, you should get a prompt to save if it was edited. You didn't say what to do about that so I assumed a prompt to save would be safe enough. That way, closing can be cancelled if needed. However, the event won't run again if cancelled. The "00:05:00" is 5 minutes & code should run 5 mins after wb is opened.
Micron thanks a lot. This was what I wanted to do. Just one more thing. If it is not read only then I want my workbook save itself if it is edited without showing prompt to user and close itself.
 
Last edited:

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,770
Office Version
  1. 365
Platform
  1. Windows
You could Google "ms excel close and save without prompt" and learn a bit about how to find answers that have been asked and answered millions of times?
Try altering the line after Else:
VBA Code:
Else
   ThisWorkbook.Close  savechanges: = true
End If
 

Forum statistics

Threads
1,176,025
Messages
5,900,962
Members
434,861
Latest member
Coffee_in_a_jiffy

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