I modified the code a bit and am now using the version below:
Sub MyMacro()
Dim i As Long
Dim wbk As Workbook

For Each wbk In Workbooks

i = Application.Workbooks.Count
If i.wbk.Name 1 Then
If IsFileOpen("I:\Ryan\Copy of Book2.xls") Then
MsgBox "File already in use!"
Else
End If
End If
Next wbk

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.
Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer

On Error Resume Next
filenum = FreeFile()
Open filename For Input Lock Read As #filenum
Close filenum
errnum = Err
On Error GoTo 0

Select Case errnum
Case 0
IsFileOpen = False
Case 70
IsFileOpen = True
End Select
End Function

I get an error on this line:
If i.wbk.Name 1 Then

I guess i.wbk.Name is an invalid qualifier. What Im trying to do is count
the number of Workbooks open and if that number 1 then prompt the user that
the Workbook is already open €“ another Workbook will NOT open.

I think this code is pretty close, but I cant seem to count the number of
Workbooks of a specific name. If a user has other Workbooks open (other
names), I dont mind. I just dont want multiple versions of THIS Workbook
open.

Thanks!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"ryguy7272" wrote:

Im running the code from he
http://support.microsoft.com/kb/291295/EN-US/

That works great if I get into the Sub and hit F5! Now, Im trying to think
of a way I can modify this slightly so as to prompt a user that the WB is
already open if they try to open the file again. The file will be saved back
to SharePoint and I want to make sure there is ONE SINGLE incidence of this
Excel WB open at a time (its fine if several other WBs are open).
Basically, I dont want someone to open the file, do some work, walk away,
forget that its open, and try to open it again (from SharePoint).

I think Im going to need something like:
workbook_open (in thisworkbook)

or

workbook_activate (in thisworkbook)

or

AUTO_OPEN (in a module)

I think, for this to work, Im going to have to count the number of WBs open
(with this WB name, right). So,

If count <=1 Then workbook_open
Else call TestFileOpened
End if

Thanks!

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.