How to stop closing all workbook after hitting X sign on top right corner.

[dmadhup]

I have two Excel file, the first one has no macro on it and the second one has a macro on it. First, i open the spreadsheet which has no macro on it. Then I open the second excel which has a macro on it. Now if I hit the X sign on the top right corner to close my first spreadsheet then it closes then it invokes the following macro of another spreadsheet. Means it force to close another spreadsheet which has following macro on it.

How can I stop to invoke the following sub upon closing the first spreadsheet?
Private Sub Workbook_BeforeClose(cancel As Boolean)

End Sub

 

[StephenCrump]

Welcome to the Forum!

I'm not totally clear what you're asking, but I think you're saying that if a particular workbook is not the ActiveWorkbook, and the user closes Excel, then that workbook should always remain open?

In which case, try:

'Code Module
Public bOtherWorkbook As Boolean
'ThisWorkbook Module
Private Sub Workbook_Activate()

    bOtherWorkbook = False

End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim wb As Workbook
    
    Cancel = bOtherWorkbook
    
    If Cancel Then
        Application.ScreenUpdating = False
        For Each wb In Workbooks
            If wb.Name <> ThisWorkbook.Name Then wb.Close
        Next
        Application.ScreenUpdating = True
    End If
    
End Sub
Private Sub Workbook_Deactivate()

    bOtherWorkbook = True
    
End Sub

For all other workbooks where changes have been made, you should get the usual dialog box allowing you to save changes or cancel the close.