wb.name won't include the path.
wb.fullname will include the path

And I'd include the .xls, too--and maybe check for case differences:

if lcase(wb.fullname) = lcase(wbfilename) then



Jenny B. wrote:

Good Afternoon,

I originally was using a differnt Macro to detect if a workbook was closed.
In the event the workbook was open, it would pop up an error message
informing the user "workbook in use, try again later". After using it in an
actual application, it failed so I wrote a basic one as shown below just to
see if my sheet was even detecting the workbook open function - it does not.

By looking at the code below, can you think of any ideas "why" my worksheet
might not be detecting if another sheet is open? I also tried this on 2
blank workbooks just to make sure no other code was interfering - it failed
there too.

Thanks for your review and thoughts - Jenny B.

Sub ChckOpenStatus()
Dim wb As Workbook
Dim wbopen As Boolean
Dim wbfilename As String

wbopen = False
wbfilename = "C:\Documents and Settings\Me\Desktop\Book2"

For Each wb In Application.Workbooks
If wb.Name = wbfilename Then
wb.Open = True
wb.Activate
MsgBox "Workbook is in use. Try again later"

End If
Next
If wbopen = False Then
Workbooks.Open wbfilename
End If
End Sub

--

Dave Peterson