Close other workbooks on open

This may be a simple question, but I can't figure out the code for it. When a user opens the workbook I am building I want it to see if other excel files are open and prompt a warning that no other excel files can be open while accessing my workbook. If they click continue then all the files will be closed and the active one will be the only file open. If they choose to not continue then only my tool closes. Also, if possible I would like the warnings to pop up with the application not visible and then regardless of the choice the application is visible again.

As I said this may be something simple. Thanks for any help provided.

Hi there,

See if the attached workbook does what you need.

It uses the following code which should be inserted into the "ThisWorkbook" VBA CodeModule of your workbook:

Option Explicit


Private Sub Workbook_Open()

    Dim iYesNo  As Integer
    Dim wbk     As Workbook

    Windows(Me.Name).Visible = False

    If Workbooks.Count > 1 Then

          iYesNo = MsgBox("No other workbooks may be open while workbook """ & _
                           Me.Name & """ is open" & _
                           vbLf & vbLf & _
                          "Do you want to close ALL open workbooks?" & _
                           vbLf & _
                          "(Any changes to open workbooks will be saved " & _
                          "automatically before the workbooks are closed)", _
                           vbYesNo + vbQuestion, "Other workbooks are open")

          If iYesNo = vbYes Then

                For Each wbk In Workbooks

                    If Not wbk Is Me Then
                        wbk.Close SaveChanges:=True
                    End If

                Next wbk

                Windows(Me.Name).Visible = True
                Me.Saved = True

          Else: MsgBox "The workbook """ & Me.Name & """ will now be closed", _
                        vbInformation, "Application not available"

                Me.Close SaveChanges:=False

          End If

    Else: Windows(Me.Name).Visible = True
          Me.Saved = True

    End If

End Sub

Hope this helps - please let me know how you get on.

Regards,

Greg M