Locked for Editing

[Office User]

We are constantly having someone open up an excel workbook within our network
and not closing it before they go home. This does not allow us to continue
adding entries into the workbook throughout the night. We are forced to save
the workbook just as a copy then the next day we have to go back and enter
all of the entries that occurred over the previous night, a lot of tedious
work. Is there any Macro or other function that would enable me to force this
document to be closed on the network? We've tried communicating the necessity
of closing the document but to no avail.

 

[ozgrid.com]

Is "Sharing" the Workbook an option? If not use the code below in
ThisWorkbook Module;

Private Sub Workbook_Open()
dTime = TimeValue("6:00:00")
Application.OnTime dTime, "CloseMe"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime dTime, "CloseMe"
On Error GoTo 0
End Sub

In a standard module, use;

Public dTime As Date

Sub CloseMe()
ThisWorkbook.Close SaveChanges:=False
End Sub

Change time save changes to suit.

 

[Office User]

I need the workbook to close from wherever it is opened at whenever a command
is made from a different computer on the network to open the workbook. Is
this possible?

 

[Dave Peterson]

I've always considered this to be a training issue.

You may be able to use a macro that closes the workbook if the user hasn't
changed the selection in some set time.

But the real problem that I see is how do you close that workbook safely? Do
you save their changes? Do you discard their changes?

If you (as the programmer) choose to save their changes, what happens if the
user has deleted some valuable data (on purpose to save as a new name or by
mistake)?

If you choose to discard their changes, what happens to the last 3 hours of work
that hasn't been saved?

I don't understand how a programmer can make that decision with any confidence.

==========
As an alternative, have you thought of using a different application -- one
that's designed to allow multiple simultaneous updates -- like a real database
(Access????).

 

[Office User]

We haven't persued any other databases I'm assuming which is due to Excel's
interface and everyone's used to using it. To convert to Access would take
approval from multiple highend parties. At this time, we're pretty much stuck
with Excel. As for your question ... It would be best to save the changes
then forcibly close the workbook. Even if someone changes data, we will
always have a way to retrieve the correct data again because it is generated
from another sheet. Furthermore, the person entering the data copies all
previous data then pastes it to this sheet, so all data is always correct
once it is pasted regardless of what someone else is doing to the sheet. For
the most part, only one station should have read-write access where everyone
else only needs a read status, but not always the case. So if I can make the
workbook save and close when someone else wants to open it, that would be my
best option.

 

[Office User]

???