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

Many thanks

Tim

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

Many thanks

Tim

The ref is likely from your Win10 machine, perhaps?

Just curious why you need to close all the windows...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

On 6/30/2016 12:50 PM, Tim Childs wrote:
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
Many thanks
Tim

What happens if you do this?
Dim W as Object

"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

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

The answer to Garry's question is that I want to close the windows to
tidy everything up - otherwise the Window you want is lost in a long
list when you switch to another window.

Thanks

Tim

The answer to Garry's question is that I want to close the windows to
tidy everything up - otherwise the Window you want is lost in a long
list when you switch to another window.

I maximize 1 window and access the others via the Project Explorer. The
VBE remembers this and opens maximized thereafter. Otherwise I don't
understand what you mean by "lost in a long list"!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

On 02/07/2016 23:38, GS wrote:
The answer to Garry's question is that I want to close the windows to
tidy everything up - otherwise the Window you want is lost in a long
list when you switch to another window.

I maximize 1 window and access the others via the Project Explorer. The
VBE remembers this and opens maximized thereafter. Otherwise I don't
understand what you mean by "lost in a long list"!


Hi Garry

Thanks for post. I mean thelong list when you use the Windows menu item
in VBE itself, where the list of open windows shows all the code windows

Best wishes

Tim

On 02/07/2016 23:38, GS wrote:
The answer to Garry's question is that I want to close the windows
to
tidy everything up - otherwise the Window you want is lost in a
long
list when you switch to another window.

I maximize 1 window and access the others via the Project Explorer.
The
VBE remembers this and opens maximized thereafter. Otherwise I
don't
understand what you mean by "lost in a long list"!


Hi Garry

Thanks for post. I mean thelong list when you use the Windows menu
item in VBE itself, where the list of open windows shows all the code
windows

Best wishes

Tim

Ah, yes! Thanks for clarifying! (I never use that menuitem as I find it
easier to select windows in ProjectExplorer)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

"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

"GS" wrote in message
On 02/07/2016 23:38, GS wrote:
The answer to Garry's question is that I want to close the windows to
tidy everything up - otherwise the Window you want is lost in a long
list when you switch to another window.

I maximize 1 window and access the others via the Project Explorer. The
VBE remembers this and opens maximized thereafter. Otherwise I don't
understand what you mean by "lost in a long list"!


Hi Garry

Thanks for post. I mean thelong list when you use the Windows menu item
in VBE itself, where the list of open windows shows all the code windows

Best wishes

Tim

Ah, yes! Thanks for clarifying! (I never use that menuitem as I find it
easier to select windows in ProjectExplorer)

It's not really to select windows but to close them. I run it from a button
on the VBE menu and it gets clicked often. Also before saving to avoid
windows opening when the file next loads.

Peter T

"GS" wrote in message
On 02/07/2016 23:38, GS wrote:
The answer to Garry's question is that I want to close the
windows to
tidy everything up - otherwise the Window you want is lost in a
long
list when you switch to another window.

I maximize 1 window and access the others via the Project
Explorer. The
VBE remembers this and opens maximized thereafter. Otherwise I
don't
understand what you mean by "lost in a long list"!


Hi Garry

Thanks for post. I mean thelong list when you use the Windows menu
item in VBE itself, where the list of open windows shows all the
code windows

Best wishes

Tim

Ah, yes! Thanks for clarifying! (I never use that menuitem as I
find it easier to select windows in ProjectExplorer)

It's not really to select windows but to close them. I run it from a
button on the VBE menu and it gets clicked often. Also before saving
to avoid windows opening when the file next loads.

Peter T

I took a look at that in v2010 and see it has a menuitem 'Close All
Windows' at the bottom.

Is this what y'all are talking about?
What does it matter how many windows are open?
(There appears to be a limit to the length of the list
*and* a 'More Windows...' option that displays more windows in
a listbox of a popup window!)

This is what I also find in v2003.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Looks like that "Close All Windows" menuitem is part of the MZ Tools
VBE addin I've been using for some years now. Didn't notice because I
don't normally use the Window menu!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

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

"GS" wrote in message

It's not really to select windows but to close them. I run it from a
button on the VBE menu and it gets clicked often. Also before saving to
avoid windows opening when the file next loads.

Peter T

I took a look at that in v2010 and see it has a menuitem 'Close All
Windows' at the bottom.

Is this what y'all are talking about?
What does it matter how many windows are open?
(There appears to be a limit to the length of the list
*and* a 'More Windows...' option that displays more windows in
a listbox of a popup window!)

This is what I also find in v2003.

I guessed that was MZ Tools :)

I have my own menuitem in the same place, it does similar but also collapses
folders in Project Explorer.

It's about convenience rather than "matter". Easier to keep only the windows
you're working with open if navigating say with say ctrl-tab or even form
the Windows dropdown. Also when a file opens that had been saved with open
modules/windows, it can take a while to open the same windows again next
time it opens. IOW it reopens with windows in the same state as saved.

Peter T

"GS" wrote in message

It's not really to select windows but to close them. I run it from
a button on the VBE menu and it gets clicked often. Also before
saving to avoid windows opening when the file next loads.

Peter T

I took a look at that in v2010 and see it has a menuitem 'Close All
Windows' at the bottom.

Is this what y'all are talking about?
What does it matter how many windows are open?
(There appears to be a limit to the length of the list
*and* a 'More Windows...' option that displays more windows in
a listbox of a popup window!)

This is what I also find in v2003.

I guessed that was MZ Tools :)

I have my own menuitem in the same place, it does similar but also
collapses folders in Project Explorer.

It's about convenience rather than "matter". Easier to keep only the
windows you're working with open if navigating say with say ctrl-tab
or even form the Windows dropdown. Also when a file opens that had
been saved with open modules/windows, it can take a while to open the
same windows again next time it opens. IOW it reopens with windows in
the same state as saved.

Peter T

Yeah, the behavior is familiar. Don't know if MZ Tools collapses the
explorer tree but hoe so because that would be a convenient tool!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Yep, it's the last icon on the toolbar!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

"GS" wrote in message
Yep, it's the last icon on the toolbar!

Which toolbar is that?

Peter T

"GS" wrote in message
Yep, it's the last icon on the toolbar!

Which toolbar is that?

Peter T

MZ Tools!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

"GS" wrote in message
"GS" wrote in message
Yep, it's the last icon on the toolbar!

Which toolbar is that?

Peter T

MZ Tools!

Ah I see it now, though it's not the last icon for me. I'll keep using mine
though as it's only one instead of two clicks to close all and collapse <g

More seriously though I only open MZ Tools when needed for a few particular
functions.

Peter T

"GS" wrote in message
"GS" wrote in message
Yep, it's the last icon on the toolbar!

Which toolbar is that?

Peter T

MZ Tools!

Ah I see it now, though it's not the last icon for me. I'll keep
using mine though as it's only one instead of two clicks to close all
and collapse <g

More seriously though I only open MZ Tools when needed for a few
particular functions.

Peter T

Ok.., it's the last function icon before the 'Other Utilities' menu.
The last icon on the bar is 'Options' which resembles the VBE 'Toolbox'
icon.

I mostly use it for custom keyboard shortcuts to toggle
comments/bookmarks/breaks and clearing the Immediate Window. I've been
wanting to customize code inserting feature but my project template has
everything I use as a base project; -all I do is 'SaveAs'!

I suppose all I need to do is create a sub to execute both those
controls if I feel I need to do them together. Don't see that happening
anytime soon, though!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus