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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.