How to close an excel sheet after a set amount of time?

[sksaini]

Hello,

We have an excel'97/2000 worksheet that everyone needs to work in. We can not make it shared.

Sometimes, one person opens it, works on it and leaves it open forever.

Is there any way, we can force the worksheet to close automatically after a set interval of time?

Thanks.

Sushil S.

 

[rdodge]

Sure is. Put the following code in your workbook file in the VBA environment. Doubleclick the workbook object shown in the project browser window, then paste the below code in it.

Option Explicit

Dim CloseTime As Date

Private Sub Workbook_Open()
CloseTime = Now + TimeValue("00:30:00&quot
Application.OnTime CloseTime, "CloseWorkbook"
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.OnTime CloseTime, "CloseWorkbook", , False
CloseTime = Now + TimeValue("00:30:00&quot
Application.OnTime CloseTime, "CloseWorkbook"
End Sub

Insert a Standard Module (it will only show the word, "Module" in the Insert Menu)

Paste the below code in this standard module window:

Option Explicit

Public Sub CloseWorkbook()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

This will cause the workbook to save and close within 30 minutes since the later of the 2:

When the workbook was openned
When the workbook last had been changed by the user

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[sksaini]

Hello Ronald,

Thanks for the solution. It worked well.

But now I have two concerns-

Everytime you open the workbook, it prompts to enable or disbale the macros - my concern is, one trouble maker may chooose to disbale the macros and then the problem will remain as such. Any way to bypass thsi message?

Second, even if someone shooses enable macros, a smart guy may find and delete the macro. Any way to hide the macro from common user or make it read-only or any other solution?

Thanks a lot for your time.

Sushil S.

 

[DrBowes]

You can protect the code, in vba editor - Tools, VBA Project Properties.

You can also use Workbook_Open Code and Workbook_Close Code that hides all the pages (except a sngle page saying that macros need to be enabled) if macros are disabled.

However if people really want to mess up your workbook they can!

 

[rdodge]

In addition to what DrBowes mentioned, people can hold down the Shift key as the file is openning to disable macros, even if the "Security" setting is set to "Low". If macros are disabled, even the macros that's in any of the objects will be disabled, not just the Standard Module and Class Module codes.

The only protection you have is the VBA Editor Password protection, but as you may know, even that password can be broken fairly easily (there's been claims of within 48 hours, even for longer thought out tough passwords).

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[SQLBI]

Hi,

To avoid the "Enable Macros" prompt, if your using Win2k or later, run SelfCert.exe. This will create a digital signature for your code.

Then go around your users, or get them to open a signed file and check the option "Always trust code form this source".

In future when someone opens a signed project, the "Enable Macros" prompt will not be displayed.

Hope this helps.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[SQLBI]

Sorry, once you've created your certificate, in the VBE, telect Tools > Digital Signature and add your cert to the project.



Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[sksaini]

Thank you all for all your help in this matter.

Sincerely,

Sushil S.

 

[sksaini]

Hello All,

I am back with an idea for a solution. Propably you guys can help in its implementation.

Is there any way not to let a worksheet/book open, if someone chooses to Disable the Macros?

The idea is to have a macro in a worksheet and let any body work in that sheet, only if they choose to enable the worksheet otherwise the worksheet should not open up at all.

Thank you all.

S.

 

[itsfisko]

great stuff I wiil use this to setup a test in excell for my pupils

Some lead, some follow....I just Hope!

 

[KenWright]

Insert an extra Sheet in your workbook, and on that sheet have some text explaining that if the user is seeing this then they must have disabled macros and therefore will be unable to use the file. Then hide all sheets except this one, and have a piece of code tied to the Workbook_Open event that unhides all the hidden sheets and hides the unhidden sheet with the text on it. Have the code make the sheets xlVeryHidden and protect the VBA project, and users will not even know that there were any sheets to unhide in the first place.

(Don't forget to have a piece of code tied to the Workbook_Close event that hides all the sheet again and unhides the sheet with text on it)

Regards
Ken...............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------