On 03-Jul-16 4:37 PM, Peter T wrote:
"Tim Childs" wrote in message
On 01-Jul-16 7:01 PM, Peter T wrote:
"Tim Childs" wrote in message
Hi

I have used the code below to close the extra VB windows that end up
being open when using the VBA editor.

Sub CloseVBEWindows()
Dim W As VBIDE.Window

ThisWorkbook.VBProject.VBE.MainWindow.SetFocus
For Each W In Application.VBE.Windows
If W.Type = vbext_wt_CodeWindow Or W.Type = vbext_wt_Designer Then
If Application.VBE.ActiveWindow.Caption < W.Caption Then
W.Close
End If
End If
Next W
End Sub

It works fine on a 64-bit laptop running Windows 10 and Excel 2010.
When I run it on a 64-bit laptop with windows 7 and Excel Professional
Plus I get the error 424 (object required) at the following line:
If W.Type = vbext_wt_CodeWindow Or W.Type = vbext_wt_Designer Then

The extensibility library 5.3 has been set in references.

Can anyone suggest a possible solution, please

That ought to work, I have something similar (and more to collapse all
projects). Curious though why you get that error there and not on the for
each line. If there was anything wrong with the reference you would have
got
a compile error. When it breaks see what W refers to in Locals, alt-v, s

If you want to try Mike's suggestion and declare W as object, also change
the named vbext constants to 0 and 1 respectively (or simply if W.Type <=
1)
to rule out the extensibility reference.

Peter T




Hi Peter

Thanks for the suggestion

I found that on the Acer machine the following code still failed in
runtime with the same error for one VBE window (see below):

Sub A_CloseVBEWindowsTEST()

Dim W As Object
Dim iCounter As Integer

ThisWorkbook.VBProject.VBE.MainWindow.SetFocus


Debug.Print "total No of windows = " & Application.VBE.Windows.Count
iCounter = Application.VBE.Windows.Count

For Each W In Application.VBE.Windows
Debug.Print "W #" & iCounter & " is currently: " & (W.Caption)

iCounter = iCounter - 1
Next W
End Sub

It produced this in the Immediate Window on another machine:

total No of windows = 9
W #9 is currently: Close VBE windows, ex Acer test on Mac TEMP.xlsm -
Sheet1 (Code)
W #8 is currently: mClearVBECodeWindows (Code)
W #7 is currently: Project - VBAProject
W #6 is currently:
W #5 is currently: Properties
W #4 is currently: Object Browser
W #3 is currently: Watches
W #2 is currently: Locals
W #1 is currently: Immediate

I think it is failing on the "blank" window i.e. Window #6 on the Acer but
am not sure - what is that window - I recognise the others?

Any help welcome


Did you try what I suggested before, look at the errant W in Locals, and the
way I suggested when it breaks after determining it's a module window. That
#6 above be the one that's causiong problems but without knowing could be
some other unrelated window.

Trivial but FWIW ThisWorkbook.VBProject.VBE.MainWindow is the same as
Application.VBE

Peter T



Hi Peter

Thanks for the response. I will try that and thanks for the FWIW tip -
always looking for simplification :)

Best wishes

Tim