Macro to only Unhide sheets if macros enabled and force user out after period of inactivit

Hi,

I need a macro to 1. only allow a user to unhide tabs only if macros are enabled and whiles file is opened force use out of file if inactive for 5 mins.. I found bits and pieces online, but having issues merging the two together..

I would appreciate if someone can help with that.. 1. only unhide tabs when macros are enabled. and 2. save and close file after 5 mins of inactivity. Thanks in advance

If you set the sheet to veryhidden it can only be unhidden from with the vba editor,
for example

Sheets(1).hidden = xlveryhidden

Note - you don't need to turn macros on to unide it, but you do need access to the vba editor.

For the second part, what do you class as inactive? not changing a cell? not moving the mouse? not changing the selected cell? not clicking within the excel environment? etc.

yudlugar


I appreciate your assistance. Yes for the second part if the cell is not changed for perhaps 2 mins, I want the excel file closing. The file is a shared file and often I find that it is locked all the time and the person in it just has it opened.

It would be nice if you can share a code that will take care of both issues.. that is only make sheets available when macros are enabled and 2. close out file after a certain period of inactivity (not changing cells). I thank you in advance

I would suggest doing this with an additional hidden sheet, call it timer_lock. Then have a worksheet_change event in every sheet that sets cell A1 of timer_lock equal to 1.

Then use the ontime method to get a macro to check A1 of timer_lock every 2 minutes. If it is 1, set it to 0. If it is 0, close the workbook.

This would look something like this:
In every sheet object module

Sub worksheet_change(byval target as range)
   sheets("timer_lock").range("A1") = 1
End sub

In a standard module:

Sub Timer_lock_macro()
if sheets("timer_lock").range("A1") = 1 then 
  sheets("timer_lock").range("A1") = 0
else
  thisworkbook.close
end if
application.ontime now() + "00:02:00"
End sub

Then in your workbook open module:

sub workbook_open()
   for each ws in thisworkbook.sheets
     if not ws.name = "front sheet name" then ws.hidden = xlhidden
   next
end sub
sub workbook_beforesave()
   for each ws in thisworkbook.sheets
     if not ws.name = "front sheet name" then ws.hidden = xlveryhidden
   next

Out of interest what is the aim of doing this? Implementing this code seems a horrendously bad idea to me.

So the reason for this is.. This is a shared file where different departments share the same file and update tabs that they are responsible for. The issue we have been running into is that often when different people try to access this file it saids it is locked by another person and sometimes the person may be away from their desks on hours and thus makes it impossible for anyone else to edit. The solution i thought is to require all to enable macros so that we can have a time element to it to avoid file always being locked