Read Only problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dwasbig9@gmail.com - 14 Dec 2007 11:15 GMT

Hi Group,

I have a spreadsheet that runs over a corporate network which is used
by a number of different people. A problem that has plagued it from
time to time is that the sheet shows as locked read only when no one
is in it (ie it could report that I was still logged in, or someone
who hadn't been at work for a few days was still logged in).

One possible contributory factor is that we have to hot desk and not
all users close excel and log out instead other users who are locked
out use the big on off button to get themselves logged into the
network. Could this be the cause? If so can excel have an event
which saves and closes the file before the lock workstation screen
kicks in?

Any thoughts on this would be welcomed.

Regards

Don

Reply to this Message

Bob Phillips - 14 Dec 2007 12:10 GMT

Try this, it closes a workbook down if not updated for 1
5 minutes

Option Explicit

'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
nElapsed = TimeSerial(0, 15, 0) '15 minutes
'start a timer to countdown inactivity
Application.OnTime Now + nElapsed, "ShutDown"
End Sub

'-----------------------------------------------------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'-----------------------------------------------------------------
'any workbook activity resets the timer
Application.OnTime Now + nElapsed, "Shutown"
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

Put this code in a standard code module

Option Explicit

Public nElapsed As Double

'-----------------------------------------------------------------
Sub ShutDown()
'-----------------------------------------------------------------
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi Group,
>
[quoted text clipped - 16 lines]
>
> Don

Reply to this Message

dwasbig9@gmail.com - 14 Dec 2007 13:43 GMT

> Try this, it closes a workbook down if not updated for 1
> 5 minutes
[quoted text clipped - 67 lines]
>
> - Show quoted text -

Hi Bob,

Thanks for your reply. The main save and close works but there is
something wrong with the reset section of code as the workbook closes
from time opened not time last changed.

PS in addition to sheet changes can the VBA recognise when the last
change of sheet tab or mouse action?

Many thanks

Don

Reply to this Message

Bob Phillips - 14 Dec 2007 15:53 GMT

This will catch a sheet change and/or a cell selection, but there is no
workbook mouse event.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'any worksheet change resets the timer
Application.OnTime Now + nElapsed, "Shutown"

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
'any worksheet selection resets the timer
Application.OnTime Now + nElapsed, "Shutown"
End Sub

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>> Try this, it closes a workbook down if not updated for 1
>> 5 minutes
[quoted text clipped - 82 lines]
>
> Don

Reply to this Message

dwasbig9@gmail.com - 14 Dec 2007 17:16 GMT

> This will catch a sheet change and/or a cell selection, but there is no
> workbook mouse event.
[quoted text clipped - 110 lines]
>
> - Show quoted text -

Hi Bob,

Thanks to your post I had an idea of what to look for and since my
last post found http://www.cpearson.com/excel/OnTime.aspx This seems
to answer the problems I was having with the timer not resetting and
always closing on a time set from open and not update.
I now have

Private Sub Workbook_Open()
Call StartTimer
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Call StopTimer

Call StartTimer
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
Call StopTimer

Call StartTimer
End Sub
Private Sub Workbook_Activate()
Call StopTimer

Call StartTimer
End Sub

and in my module

Public RunWhen As Double
Public Const cRunIntervalSeconds = 10 ' two minutes
Public Const cRunWhat = "The_Sub" ' the name of the procedure to run

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=True
End Sub

Sub The_Sub()
ThisWorkbook.Save

ThisWorkbook.Close
'MsgBox "Timer Started"

'''''''''''''''''
' Your Code Here
'''''''''''''''''
' Call StartTimer to schedule the procedure again
'StartTimer
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=False
End Sub

Which came from Chip Pearsons site

Thanks for your help and your most recent post I've add the
sheetactivate event to my list of triggers.

Don

Reply to this Message

dwasbig9@gmail.com - 02 Jan 2008 12:31 GMT

Hi Group,

Happy New Year to you all. I'm carrying on with my timer project and
found that the solution I posted was not without its problems for my
situation. I have since found out how to store and call a procedure
from my personal.xls file which now starts and stops the timer from a
central location and have also included a "stop timer" "start timer"
On Open, to keep the timeframe to Close as I needed.

What I now need to find out is how to close all open workbooks that
match/ include part of a file name. ie if the filename includes the
text MyFileName it closes MyFileName1.xls MyFileName2.xls etc

As always any help would be appreciated.

Don

Reply to this Message

Dave Peterson - 02 Jan 2008 14:41 GMT

dim wkbk as workbook
for each wkbk in application.workbooks
if wkbk.name = thisworkbook.name then
'skip it
else
if lcase(wkbk.name) like lcase("myfilename*.xls") then
wkbk.close savechanges:=true 'false???
end if
end if
next wkbk

How are you ever going to decide to save a workbook with changes that the user
may not want saved? Or close a workbook without saving when the user wants to
save those changes.

This sounds like you could have a either group of users coming after you with
pitchforks!

> Hi Group,
>
[quoted text clipped - 12 lines]
>
> Don

Signature

Dave Peterson

Reply to this Message

donh - 02 Jan 2008 15:33 GMT

> dim wkbk as workbook
> for each wkbk in application.workbooks
[quoted text clipped - 38 lines]
>
> - Show quoted text -

Hi Dave,

Thanks for your reply I'll see if I can apply it.

You may be right in what you say, time and testing will see.

FYI the VBA that I'm trying to construct will only be called by a
certain group of workbooks which deal in rota management (hence the
reason for only wanting to close certain variations of a filename).
The workbooks are used over a corporate network to remote sites and we
are having problems with files being locked read only, which might be
caused by users not closing down excel before the computer is locked
(either done by the user or after a period of inactivity). As I work
for a service whose employees could be called out at any moment on a
call, the user who is logged in is not always around to unlock the
computer and the only coarse of action is to turn the computer off and
start again. The code I'm trying to come up with will save and close
the excel sheets within this group of workbooks after a given time of
inactivity and hopefully before anyone turns the computer of.

If there is another way I can tackle this problem I would welcome any
feedback.

Don

Reply to this Message

Dave Peterson - 02 Jan 2008 16:51 GMT

The only way I know is to make it a training issue.

I think any assumption that you make (or are forced to make) will be wrong and
will have a significant impact on at least one user or file.

Keep plenty of backups and remember to say: I only did what I was told to do.

<vbg>

<<snipped>>

> Hi Dave,
>
[quoted text clipped - 20 lines]
>
> Don

Signature

Dave Peterson

Reply to this Message

donh - 02 Jan 2008 17:25 GMT

Dave,

Thanks for words of wisdom. My test file seems to work ok. Its a
fairly simple workbook keeping count of the number of people at work,
accessed by lots of people but not altered by many, so time and
testing will tell if its worthwhile.

Perhaps its a different slant on a training need, if you alter the
workbook and leave it to close itself, don't be surprised if its saves
without you!

Thanks for your help

Don

Reply to this Message

Dave Peterson - 02 Jan 2008 18:09 GMT

If the user destroys 90% of the workbook with the intent of closing without
saving, you may have lots of people upset.

If the user spends 2 hours doing data entry and you close without saving, you'll
only have one irate user.

You may want to consider using a different application--something that's
designed to allow multiple users to access/update the data. Maybe a database
program like Access???

> Dave,
>
[quoted text clipped - 10 lines]
>
> Don

Signature

Dave Peterson

Reply to this Message

donh - 03 Jan 2008 09:56 GMT

Dave,

Thanks for comments. Shame I can't show you the spreadsheet as its
not always easy to put over the relevant info up front. The workbook
is pretty well tied down and whilst not in the same league as
professional work its something I'm very proud of. Its been designed
with custom levels of password protection, local managers can write,
other users read only, historical data requires a further password to
change, VBA cell protection is in place, and no macros enabled = a
prompt only worksheet displayed. At present it only has a save/close
option, but I'm about to change that so it allows a save option as
well (the new save sequence hides all sheets (very hidden) apart from
prompt sheet, saves, then un hides sheets, keeping the prompt only
worksheet visible in the latest saved version. Save close does the
same sort of thing).

Sadly I'm coming to the end of my career (came to excel late in life)
and although I had hoped of getting this into a database (started to
learn MySQL and PHP to get it web based) I guess I'm running out of
time and will have to leave that move to someone else, but I do agree
it should be in another app.

Thanks again for your help

Don

Reply to this Message

Dave Peterson - 03 Jan 2008 14:46 GMT

It sounds like quite a project!

> Dave,
>
[quoted text clipped - 21 lines]
>
> Don

Signature

Dave Peterson

Reply to this Message