Close worksheet

J

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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
What do mean by an "open sheet"? Sheets can be visible or hidden; I've not heard "open" and "closed." Also please show the code you are using for your macro button. If we don't know how you're opening them we don't know how to close them.

If by "closed" you mean "hidden", it is not possible to hide all sheets. You must have at least one visible sheet.
 
Upvote 0
6StringJazzer said:
What do mean by an "open sheet"? Sheets can be visible or hidden; I've not heard "open" and "closed." Also please show the code you are using for your macro button. If we don't know how you're opening them we don't know how to close them.

If by "closed" you mean "hidden", it is not possible to hide all sheets. You must have at least one visible sheet.
Click to expand...
You can have more than 1 sheet open by activating another sheet as 'new window' in the view tab, like that you can have multiple sheets open, side by side or one on top of the other. when you want to close that book, you'de have to close each window (sheet) by clicking the X button on upper right corner. In one of the sheets named "Master Sheet" I need a vba code that'll prompt the closure of all open sheets when i close that sheet (Master Sheet)
 
Upvote 0
You do not have to close multiple windows to be able to close the file. You can just close the file. All windows close, then the next time you open it those same windows open up again with the same sheets they had when it was closed.

they're opened with a macro button as new window
Click to expand...
As I said, to provide code to close the sheets, please provide the code you already have.
 
Upvote 0
Hi, with multiple sheets (of same workbook) open in other windows (as in new window) the file does not close when i close the active sheet, the workbook remains open, and it moves on to the next open sheet, the file only closes when the last open sheet is being closed.
In my case the MasterSheet is like the main page on a proram with the other sheets being subpages. when the main sheet is closed i want the entire file/workbook to close, including all other sheets (of same workbook) that are open in other windows.
I know i can close a file with multiple open sheets by clicking the X button on the upper right corner while holding the shift key. but i want it to be automatc everytime i close the MasteSheet.

here is the code i tried

Private Sub Worksheet_Deactivate()
If ActiveSheet.Deactivate Then
ActiveWorkbook.Close
End If
End Sub

I first tried without If Then when it didnt work i tried with If/then but still dosent work.

thank you
 
Upvote 0
I don't think there is an event that captures the closing of a window . Maybe someone can think pof a workaround.
Is this in excel 2010 ?
 
Upvote 0
I asked this before but let me try again.
they're opened with a macro button as new window
Click to expand...
Please show this code. I would suggest using code tags (highlight code then click the VBA button)

I don't have an old version of Excel to test with but in newer versions you just File > Close and all the windows close. Then the next time you open the file, the same windows open again. I think this happens back to 2007.
 
Upvote 0
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
 
Upvote 0
6StringJazzer said:
I asked this before but let me try again.

Please show this code. I would suggest using code tags (highlight code then click the VBA button)

I don't have an old version of Excel to test with but in newer versions you just File > Close and all the windows close. Then the next time you open the file, the same windows open again. I think this happens back to 2007.
Click to expand...
Thanks for your help and patience
The sheets (other than "Master Sheet") are opened with a button on a userform which pops up when i click a macro button on the master Sheet. The user form has about 30 buttons each button openes a specific sheet. See VBA below

VBA Code: 
Sub Powder_80_Light_1_Click()
    ActiveWindow.NewWindow
        Sheets("80 Light, Powder 1").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
    Add_Batch.Hide
End Sub
Private Sub Powder_80_Light_2_Click()
    ActiveWindow.NewWindow
        Sheets("80 Light, Powder 2").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Fluid_80_Light_1_Click()
    ActiveWindow.NewWindow
        Sheets("80 Light, Fluid 1").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Fluid_80_Light_2_Click()
    ActiveWindow.NewWindow
        Sheets("80 Light, Fluid 2").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Bakers_Powder_Click()
ActiveWindow.NewWindow
        Sheets("Bakers, Powder").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Bakers_Fluid_Click()
ActiveWindow.NewWindow
        Sheets("Bakers, Fluid").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Chocolate_leben_fluid_Click()
ActiveWindow.NewWindow
        Sheets("Chocolate Leben, Cream").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Chocolate_Leben_Recombined_Click()
ActiveWindow.NewWindow
        Sheets("Chocolate Leben, Water").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Bulk_CreamCheese_1_Click()
ActiveWindow.NewWindow
        Sheets("Bulk Cream Cheese 1").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Bulk_CreamCheese_2_Click()
ActiveWindow.NewWindow
        Sheets("Bulk Cream Cheese 2").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Bulk_CreamCheese_3_Click()
ActiveWindow.NewWindow
        Sheets("Bulk Cream Cheese 3").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Whipped_CreamCheese_1_Click()
ActiveWindow.NewWindow
        Sheets("Whipped Cream Cheese 1").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Whipped_CreamCheese_2_Click()
ActiveWindow.NewWindow
        Sheets("Whipped Cream Cheese 2").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Original_CreamCheese_Click()
ActiveWindow.NewWindow
        Sheets("Original Cream Cheese").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Greek_1_Click()
    ActiveWindow.NewWindow
        Sheets("Greek 1").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Greek_2_Click()
ActiveWindow.NewWindow
        Sheets("Greek 2").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Greek_3_Click()
ActiveWindow.NewWindow
        Sheets("Greek 3").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Greek_4_Click()
ActiveWindow.NewWindow
        Sheets("Greek 4").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Greek_5_Click()
        Sheets("Greek 5").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub creme_Click()
ActiveWindow.NewWindow
        Sheets("Crème").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Fullfat_Click()
ActiveWindow.NewWindow
        Sheets("Fullfat").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Greek_Lowfat_1_Click()
ActiveWindow.NewWindow
        Sheets("Greek Lowfat 1").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Greek_Lowfat_2_Click()
Sheets("Greek Lowfat 2").Activate
Add_Batch.Hide
End Sub
Private Sub Greek_Lowfat_3_Click()
ActiveWindow.NewWindow
        Sheets("Greek Lowfat 3").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Greek_Lowfat_4_Click()
ActiveWindow.NewWindow
        Sheets("Greek Lowfat 4").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Leben_Powder_Click()
ActiveWindow.NewWindow
        Sheets("Leben, Powder").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Eshel_Powder_Click()
ActiveWindow.NewWindow
        Sheets("Eshel, Powder").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Lowfat_Powder_Poppers_Click()
ActiveWindow.NewWindow
        Sheets("Lowfat, Powder, Poppers").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Lowfat_Powder_1_Click()
ActiveWindow.NewWindow
        Sheets("Lowfat, Powder 1").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Lowfat_Powder_2_Click()
ActiveWindow.NewWindow
        Sheets("Lowfat, Powder 2").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Lowfat_Fluid_1_Click()
ActiveWindow.NewWindow
        Sheets("Lowfat, Fluid 1").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Lowfat_Fluid_2_Click()
ActiveWindow.NewWindow
        Sheets("Lowfat, Fluid 2").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Pastry_Powder_Click()
ActiveWindow.NewWindow
Sheets("Pastry, Powder").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Pastry_Fluid_Click()
ActiveWindow.NewWindow
        Sheets("Pastry, Fluid").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Sour_cream_Click()
ActiveWindow.NewWindow
        Sheets("Sour Cream").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Sour_Cream_Israeli_Click()
ActiveWindow.NewWindow
        Sheets("Sour Cream, Israeli").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Taste_Powder_1_Click()
ActiveWindow.NewWindow
        Sheets("Taste, Powder 1").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Taste_Powder_2_Click()
ActiveWindow.NewWindow
        Sheets("Taste, Powder 2").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hidee
End Sub
Private Sub Lowfat_1Percent_Powder_Click()
ActiveWindow.NewWindow
        Sheets("1% Lowfat, Powder").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Lowfat_1Percent_Fluid_Click()
        ActiveWindow.NewWindow
        Sheets("1% Lowfat, Fluid").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Cheese_Snack_Click()
ActiveWindow.NewWindow
        Sheets("Cheese Snack, Regular").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Cream_Cheese_Spreadable_Click()
ActiveWindow.NewWindow
        Sheets("Cream Cheese, Spreadable").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Veg_CreamCheese_Click()
ActiveWindow.NewWindow
        Sheets("Vegetable Cream Cheese, Spread").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Impastata_Click()
ActiveWindow.NewWindow
        Sheets("Impastata").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
Private Sub Mascarpone_Click()
ActiveWindow.NewWindow
        Sheets("Mascarpone").Select
            Application.WindowState = xlNormal
            Application.Top = 25
            Application.Left = 550
            Application.Width = 600
            Application.Height = 600
Add_Batch.Hide
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Auto Save and Close all open files
2
Replies
13
Views
770
julhs
J
G
  • Question
Select an Open workbook and apply a macro1 contained in the new book to the new book
Replies
1
Views
133
LazyBug
LazyBug
drom
  • Question
My code line close Excel App
Replies
4
Views
175
drom
drom
W
  • Question
a backup nightmare (may cause depression)
Replies
6
Views
439
johnnyL
J
J
  • Question
Closing (Hiding) multiple visible tabs.
Replies
1
Views
55
bobsan42
bobsan42

Forum statistics

Threads
1,214,985
Messages
6,122,605
Members
449,089
Latest member
Motoracer88

Share this page

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