restrict opening Excel file residing on a shared drive to one user
 

Excell 2000 - I have a worksheet that performs certain functions and then
saves the file when the workbook is opened. Can I restrict opening this
workbook which is on a shared drive to one user at a time. I do not need a
"read-only" option because the second person opening the workbook in "read
only" would encounter an error due to the automatic saving of the file.

Is it ok to close the workbook whenever it's opened in readonly mode?

If yes, you could add something like this to your workbooks
auto_open/workbook_open code:

Option Explicit
Sub auto_open()
With ThisWorkbook
If .ReadOnly Then
MsgBox "Can't open in readonly mode!"
.Close savechanges:=False
End If
End With
End Sub

But the user has to have macros enabled for this to work.

=====
Alternatively, you could password protect that workbook--so no one can open it
directly.

Then give the users a second workbook to open the real workbook. That "helper"
workbook can check to see if the real workbook is already open.

Option Explicit
Sub auto_open()

Dim RealWkbkName As String
Dim RealWkbkPswd As String

RealWkbkName = "c:\my documents\excel\book1.xls"
RealWkbkPswd = "hi"

' Test to see if the file is open.
If IsFileOpen(RealWkbkName) Then
MsgBox "File already in use!" & vbLf & "Please try later."

Else
Workbooks.Open filename:=RealWkbkName, Password:=RealWkbkPswd
End If
ThisWorkbook.Close savechanges:=False
End Sub

' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns
' False. Otherwise, a run-time error will occur because there is
' some other problem accessing the file.

Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer

On Error Resume Next ' Turn error checking off.
filenum = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum ' Close the file.
errnum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.

' Check to see which error occurred.
Select Case errnum

' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False

' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True

' Another error occurred.
Case Else
Error errnum
End Select
End Function

The IsFileOpen function is stolen from:
http://support.microsoft.com?kbid=138621

And be sure to protect the VBA project for this helper workbook--or someone may
see the password.

Inside the VBE with your project selected:
tools|VBAProject Properties|Protection tab.

Remember all your passwords!

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
k-ham wrote:

Excell 2000 - I have a worksheet that performs certain functions and then
saves the file when the workbook is opened. Can I restrict opening this
workbook which is on a shared drive to one user at a time. I do not need a
"read-only" option because the second person opening the workbook in "read
only" would encounter an error due to the automatic saving of the file.

--

Dave Peterson