Close worksheet

JackGla

New Member
Joined
Mar 22, 2022
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
Hi, I'm working on a workbook that has multiple sheets open in that same book, (they're opened with a macro button as new window) I need a vba code that closes all open sheets and then the entire book (after promptin save changes yes/no) whenever i close a particular sheet.
I tried many different codes, didnt work.
Thank you
 
See if this works for you :

In the ThisWorkbook Module:
VBA Code:
Option Explicit

Private bClosing As Boolean
Private WindsCount As Long
Private Const TARGET_SHEET_NAME = "Master Sheet"  '<== change sheet name as required

Private Sub Workbook_WindowDeactivate(ByVal wn As Window)
    If bClosing Then bClosing = False: Exit Sub
    WindsCount = Me.Windows.Count
    If ActiveSheet.Name = TARGET_SHEET_NAME And WindsCount > 1 And Not ManyMasterSheetsActive Then
        Application.OnTime Now, Me.CodeName & ".CloseNow"
    End If
End Sub

Private Sub CloseNow()
    If Me.Windows.Count < WindsCount Then
        If ActiveSheet.Name <> TARGET_SHEET_NAME Then
            Me.Close
        End If
    End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    bClosing = True
End Sub

Private Function ManyMasterSheetsActive() As Boolean

    Dim wnd As Window
    Dim lCount As Long
  
    For Each wnd In Me.Windows
        If wnd.ActiveSheet.Name = TARGET_SHEET_NAME Then
            lCount = lCount + 1
            If lCount > 1 Then
                ManyMasterSheetsActive = True
                Exit For
            End If
        End If
    Next wnd

End Function
Yes, it does a good job closing all other open sheets of that book, the only problem is that its closing the "MasterSheet" before the other sheets, and its still posible to have the other sheets open with the main "MasterSheet" closed. My goal is to not have it possible to have the other sheets open after closing the "Master Sheet".
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top