Consulting

Results 1 to 6 of 6

Thread: Solved: Macro to close all windows but one

  1. #1

    Solved: Macro to close all windows but one

    Hi there Everyone. I have an excel workbook which uses many worksheets. There is a special view when I need to have two windows opened (because I need to see two worksheets at once). Also for easier usage I made a userform which helps people to choose the views. I would like to make a macro which checks how many windows are open, and closes all but one. So basicly closes all the opened windows, and leaves only one and maximizes it. I hope you can help! Thank you very much!

  2. #2
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    Try this...
    This will close all but active workbook.

    [vba]Sub Closer()

    For Each Workbook In Workbooks
    If Workbook.Name <> ActiveWorkbook.Name Then
    Workbook.Close False
    End If
    Next

    Application.WindowState = xlMaximized

    End Sub[/vba]
    Hope this helps
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  3. #3
    Whatever your workbook object is, there's a property called windows, which is a collection of all the window objects for your workbook. So you can basically just make a loop that says:

    while MyWorkbook.Windows.Count > 1
    myworkbook.windows(2).close
    wend
    myworkbook.windows(1).windowstate = xlmaximized

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Do you mean all extra windows for a particular workbook?

    [vba]

    Dim wndw As Window

    With ActiveWorkbook

    Do

    ActiveWindow.Close
    Loop Until .Windows.Count = 1
    End With
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Quote Originally Posted by xld
    [vba]Dim wndw As Window

    With ActiveWorkbook

    Do

    ActiveWindow.Close
    Loop Until .Windows.Count = 1
    End With
    [/vba]
    I think that otherwise[VBA]Dim wndw As Window

    With ActiveWorkbook

    Do Until .Windows.Count = 1

    ActiveWindow.Close
    Loop
    End With[/VBA]If only one window open then ...


    Artik

  6. #6
    Hi Artik!

    Yes..your version is better, because if there is onyl one window, it will not close it, while xld's macro closed the last one as well! Thank you for your help for everyone!

    As for Georgiboy's macro I get an error for the variable "workbook" in the line: For each workbook in workbooks. Anyway, I needed this macro for worksheets and not for workbooks! Thank you anyway for your help and effort

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •