How do I handle Multiple windows for a single Excel Workbook 1

[nodrog77]

Note: Excel 2003 and XP operating system.

Hello All,

I had an issue with trying to identify/count how many windows I have open for a particular workbook in VBA.

Then as I was typing it in here I worked it out. So I thought I might post it as a hint to see what you People think.

If for example I have two files open and three windows
( "TheFile.XLS" ,"TheOtherFile.XLS:1", "TheOtherFile.XLS:2")

I may need to identify and manipulate the "excess" windows before I can continue.

In this example I close them off so I can then refer to just a single window, Windows(TheFileName) from then on.



TheFileName = “TheOtherFile.XLS”

If Workbooks(TheFileName).Windows.Count > 1 Then
Do While Workbooks(TheFileName).Windows.Count > 1
Application.Windows(TheFileName& ":2").Close
Loop
End If



Any Comments are welcome - and thanks for the prompt help I have got from you all over the years.

Lea.

 

[SkipVought]

FYI, as a 15+ year Excel VBA coder, I can count the number of times I have manipulated windows, on one finger, maybe two.

I almost always reference multiple workbook and worksheets, using the workbook object or worksheet object, and have no regard for the window object at all.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

Window's caption is a good place to put descriptive name for an excel application:

ThisWorkbook.Windows(1).Caption = "excel application description"

The above code has to run every time the workbook is opened - excel does not store window names.

In this case the more secure code for closing extra windows could be:

Dim wn As Window
For Each wn In Workbooks(TheFileName).Windows
    If wn > 1 Then wn.Close
Next wn

combo

 

[combo]

I missed 'Index' in the second code box, should be:

Dim wn As Window
For Each wn In Workbooks(TheFileName).Windows
    If wn.Index > 1 Then
wn.CloseNext wn

combo

 

[fumei]

While (of course) not an Excel person, I have to agree with Skip in principle.

I just about never, ever, use the Windows object when using VBA. Why? The main point of VBA, sort of, is to do things behind the visual GUI. NOT using what is on screen, but the objects themselves, allows the execution of instructions significantly faster.

Anything that is in the Windows collection is also accessible directly as an object, be it a Workbook, a Sheet, or a Document. So why use the Windows collection? It is the same reason we do not use Activate, and avoid selecting things. Except for the rare occasion..we do not need to.

That being said, I can see tyhat there could indeed be a need for something like this. People manually open other windows, and yes it could be needed to test against what windows are open.


unknown

 

[nodrog77]

Combo,

I'm having some issues with using the index property of
the Windows object:

For Each wn In Workbooks(ThisMacroWorkbook).Windows
If wn.Index > 1 Then wn.Close
Next wn

One the first run, all windows but one are closed (Good), but on the second run wn.index = 3 but there is only a single window for that workbook open?? Next time wn.index = 2?? I admit also that I am not overly familiar with using index properties either.

I'm not sure whats happening but I might stick with .count for the moment.

 

[combo]

Windows is a property both of application and of workbook, it returns collections for the whole excel instance or specific workbook. Although windows are the same but their indexes in two collections are in general different. Which index do you test (in your initial code there is Application.Windows(..)..)?
I have no problems with indexes after:

MsgBox ThisWorkbook.Windows.Count
For Each wn In ThisWorkbook.Windows
MsgBox wn.Index
If wn.Index > 1 Then wn.Close
Next wn

Each time a window with Index=2 is deleted, the second run returns 1 twice.

Gery,
a quick example for working with window is a sheet-based application.
One can add to the worhsheet buttons or use shapes as buttons (BTW, an interesting effects in case of action buttons pasted from powerpoint). Next set window state to normal, resize it and set position, set background. After changing the caption and protecting window the worksheet could become an alternative to some userform solutions.


combo

 

[nodrog77]

Skip,
I can see your point exactly. Mind you I wouldn't mind some code to close off excess windows in the VBA Code editor in one hit ;-P