Only allow one user at a time

[Guest]

We have an Excel spreadsheet that sits on the network.
People need to open the file to be able to sign up for various duty rosters.
We would like for the file to open for the first person.
And then for any others after that, get a message that the file is in use
WITHOUT the option to open a read-only copy.
Our staff can't read and they keep opening additional copies of the file!
I have read about sharing the file and I don't think that would make things
any better.
Thanks,
Lynn

 

[Bob Phillips]

Private Sub Workbook_Open()
If ThisWorkbook.ReadOnly Then
MsgBox "File already in use"
ThisWorkbook.Close savechanges:=False
End If
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


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Harald Staff]

Hi Lynn

You would need macros running to prevent that I think. Perhaps checking if
the file is readonly in the workbook_open event and close it there, or some
barrier in the before_save event.

People can't read, that is true. If they expect to see a worksheet and they
see a dialog instead (often containing a text they don't understand), they
close the litle red X to make the dialog go away so that they hopefully can
see what they wanted to see. That is human, even the smartest people do
that, as developers we just have to live with it.

HTH. Best wishes Harald

 

[Guest]

Bob, Thanks so much. This works great.
Any chance you know how to change the macro security level on the fly?
Lynn

 

[Bob Phillips]

You can't. Think about it, it would be pretty useless if you could.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Harlan Grove]

Bob Phillips wrote...

Private Sub Workbook_Open()
If ThisWorkbook.ReadOnly Then
MsgBox "File already in use"
ThisWorkbook.Close savechanges:=False
End If
End Sub

....

Easily defeated by disabling macros.

There are times when an indirect approach works best. Using one
workbook or a script to open a different workbook means you can insist
the user run macros to get at the workbook of interest, and those
macros could check for the existence of temporary marker files to see
if the workbook of interest were already in use or not.

 

[Harald Staff]

Right you are, Harlan. This is one of the better places to use a
"switchboard workbook". If not save the data externally that is.

The problem in these cases is to hide the real file good enough, so that
users can't open it within Word

Best wishes Harald

 

[Harlan Grove]

Harald Staff wrote...

Right you are, Harlan. This is one of the better places to use a
"switchboard workbook". If not save the data externally that is.

The problem in these cases is to hide the real file good enough, so that
users can't open it within Word

....

More secure using compiled EXEs. Files on Windows file servers can be
executable but not listable. Further, they could be executable under
different credentials than the users. It's not that difficult to make
files effectively invisible to and unexecutable by users except via
specific EXE or script interfaces. 'Not that difficult' in the context
of someone as comfortable manipulating file system permissions, that
is, but IT staff should be able to figure that out.