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

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

Sorry to be a pest, but could I bother you to change the original code below
to reflect your new thoughts?

I made the edits and tried just a few moments ago, but got the something as
before - nada - no detection. I just want to make sure I'm applying your
code correctly and adding your suggestions exactly as you had intended.

Thanks again - Jenny B.

"Dave Peterson" wrote:

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

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

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

For Each wb In Application.Workbooks
If lcase(wb.Name) = lcase(wbfilename) Then
wbOpen = True '<-- fixed typo
wb.Activate
msgBox "Workbook is in use. Try again later"
exit for '<-- added. No reason to keep looking for another match
End If
Next wb
If wbopen = False Then
Workbooks.Open wbfilename
End If
End Sub

Jenny B. wrote:

Sorry to be a pest, but could I bother you to change the original code below
to reflect your new thoughts?

I made the edits and tried just a few moments ago, but got the something as
before - nada - no detection. I just want to make sure I'm applying your
code correctly and adding your suggestions exactly as you had intended.

Thanks again - Jenny B.

"Dave Peterson" wrote:

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


--

Dave Peterson

Thanks so much Dave.

Appreciate you going the extra mile to help me out.

Have a great rest of the day €“ Jenny B.


"Dave Peterson" wrote:

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

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

For Each wb In Application.Workbooks
If lcase(wb.Name) = lcase(wbfilename) Then
wbOpen = True '<-- fixed typo
wb.Activate
msgBox "Workbook is in use. Try again later"
exit for '<-- added. No reason to keep looking for another match
End If
Next wb
If wbopen = False Then
Workbooks.Open wbfilename
End If
End Sub

Jenny B. wrote:

Sorry to be a pest, but could I bother you to change the original code below
to reflect your new thoughts?

I made the edits and tried just a few moments ago, but got the something as
before - nada - no detection. I just want to make sure I'm applying your
code correctly and adding your suggestions exactly as you had intended.

Thanks again - Jenny B.

"Dave Peterson" wrote:

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


--

Dave Peterson