Check for open workbooks

[Guest]

How do i check to find out if any other workbooks are open?

I need to do this check in a macro therefore require the VBA code.

I have a workbook with a macro in, when a user runs this macro i need it to
check to see if any other workbooks are open. This macro will not run
correctly if others are open.

I am using Office 2000 - SP3

 

[Adrian D. Bailey]

function bookcount()
bookcount=workbooks.count
end function

For your macro to work, you need bookcount to be 1
--
Adrian D.Bailey, Information and Systems Manager, Dept.Human Sciences
Loughborough University, Loughborough Leics, LE11 3TU, UK.
(e-mail address removed) Tel: 01509 223007 Fax: 01509 223940

Community Warden, Storer and Burleigh Areas. Out-of-hours Tel: 01509 563263

 

[Mike Fogleman]

You will need to trap for the Personal.xls workbook which will always be
open if the user has one.

Sub CountWBOpen()
Dim wb As Workbook
Dim x As Integer

x = Workbooks.Count
For Each wb In Workbooks
If wb.name = "Personal.xls" Then
x = x - 1
End If
Next
If x > 1 Then
MsgBox ("more than 1")
Exit Sub
Else
MsgBox ("only 1")
End If
End Sub

Mike F

 

[Guest]

Thanks, Just what i was after, A simple short piece of code.