PDA

View Full Version : Solved: Macro to close all windows but one



hunsnowboard
01-09-2009, 04:49 AM
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!

georgiboy
01-09-2009, 06:51 AM
Try this...
This will close all but active workbook.

Sub Closer()

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

Application.WindowState = xlMaximized

End Sub
Hope this helps

jfournier
01-09-2009, 06:52 AM
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

Bob Phillips
01-09-2009, 06:55 AM
Do you mean all extra windows for a particular workbook?



Dim wndw As Window

With ActiveWorkbook

Do

ActiveWindow.Close
Loop Until .Windows.Count = 1
End With

Artik
01-09-2009, 08:42 AM
Dim wndw As Window

With ActiveWorkbook

Do

ActiveWindow.Close
Loop Until .Windows.Count = 1
End With
I think that otherwiseDim wndw As Window

With ActiveWorkbook

Do Until .Windows.Count = 1

ActiveWindow.Close
Loop
End WithIf only one window open then ...


Artik

hunsnowboard
01-09-2009, 10:31 AM
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