detect if other workbooks are open

[wilg]

Hi, I would like to know how to detect if there are any other workbooks open when my workbook opens. If there are other workbooks open...

1. Option code to close the other workbooks.

2. I want to msgbox a warning to close othere workbooks.

[Paul_Hossler]

Maybe


Option Explicit

'this will only work for the workbooks open in this instance of Excel
Private Sub Workbook_Open()
Dim wb As Workbook
Dim aryWB() As Workbook
Dim i As Long

ReDim aryWB(0 To 0)
Set aryWB(UBound(aryWB)) = ThisWorkbook

For Each wb In Application.Workbooks

If wb Is aryWB(0) Then GoTo NextOne

With wb
'leave add-ins
If .IsAddin Then GoTo NextOne
'leave hidden workbooks, like personal.xlsm
If .Windows(1).Visible = False Then GoTo NextOne

ReDim Preserve aryWB(UBound(aryWB) + 1)
Set aryWB(UBound(aryWB)) = wb
End With
NextOne:
Next
For i = LBound(aryWB) + 1 To UBound(aryWB)
If MsgBox(aryWB(i).Name & " is open" & vbLf & vbLf & _
"Do you want to close it without saving?", vbQuestion + vbYesNo + vbDefaultButton2) = vbYes Then
aryWB(i).Close False
End If
Next i
End Sub


Paul