Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109031 > unrolled thread

Error in VB Editor Coding

Back to article view | Back to microsoft.public.excel.programming

Contents

  Error in VB Editor Coding Tim Childs <tsnip1NOTTHIS@yahoo.co.uk> - 2016-06-30 20:50 +0100
    Re: Error in VB Editor Coding GS <gs@v.invalid> - 2016-06-30 19:44 -0400
    Re: Error in VB Editor Coding Mike S <mscir@yahoo.com> - 2016-06-30 17:41 -0700
    Re: Error in VB Editor Coding "Peter T" <askformy@gmail.com> - 2016-07-01 19:01 +0100
      Re: Error in VB Editor Coding Tim Childs <tsnip1NOTTHIS@yahoo.co.uk> - 2016-07-02 18:48 +0100
        Re: Error in VB Editor Coding GS <gs@v.invalid> - 2016-07-02 18:38 -0400
          Re: Error in VB Editor Coding Tim Childs <tsnip1IgnoreThis@yahoo.co.uk> - 2016-07-03 05:11 +0100
            Re: Error in VB Editor Coding GS <gs@v.invalid> - 2016-07-03 00:16 -0400
              Re: Error in VB Editor Coding "Peter T" <askformy@gmail.com> - 2016-07-03 17:02 +0100
                Re: Error in VB Editor Coding GS <gs@v.invalid> - 2016-07-03 14:49 -0400
                  Re: Error in VB Editor Coding GS <gs@v.invalid> - 2016-07-03 16:38 -0400
                  Re: Error in VB Editor Coding "Peter T" <askformy@gmail.com> - 2016-07-04 11:31 +0100
                    Re: Error in VB Editor Coding GS <gs@v.invalid> - 2016-07-04 06:39 -0400
                      Re: Error in VB Editor Coding GS <gs@v.invalid> - 2016-07-04 06:41 -0400
                        Re: Error in VB Editor Coding "Peter T" <askformy@gmail.com> - 2016-07-04 12:18 +0100
                          Re: Error in VB Editor Coding GS <gs@v.invalid> - 2016-07-04 07:23 -0400
                            Re: Error in VB Editor Coding "Peter T" <askformy@gmail.com> - 2016-07-04 12:49 +0100
                              Re: Error in VB Editor Coding GS <gs@v.invalid> - 2016-07-04 09:20 -0400
        Re: Error in VB Editor Coding "Peter T" <askformy@gmail.com> - 2016-07-03 16:37 +0100
          Re: Error in VB Editor Coding Tim Childs <tsnip1NOTTHIS@yahoo.co.uk> - 2016-07-03 22:02 +0100

#109031 — Error in VB Editor Coding

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

[toc] | [next] | [standalone]

#109032

> 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

[toc] | [prev] | [next] | [standalone]

#109033

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

[toc] | [prev] | [next] | [standalone]

#109034

"Tim Childs" <tsnip1NOTTHIS@yahoo.co.uk> 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

[toc] | [prev] | [next] | [standalone]

#109036

On 01-Jul-16 7:01 PM, Peter T wrote:
> "Tim Childs" <tsnip1NOTTHIS@yahoo.co.uk> 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

[toc] | [prev] | [next] | [standalone]

#109037

> 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

[toc] | [prev] | [next] | [standalone]

#109038

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

[toc] | [prev] | [next] | [standalone]

#109039

> 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

[toc] | [prev] | [next] | [standalone]

#109041

"GS" <gs@v.invalid> 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

[toc] | [prev] | [next] | [standalone]

#109042

> "GS" <gs@v.invalid> 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

[toc] | [prev] | [next] | [standalone]

#109043

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

[toc] | [prev] | [next] | [standalone]

#109046

"GS" <gs@v.invalid> 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

[toc] | [prev] | [next] | [standalone]

#109047

> "GS" <gs@v.invalid> 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

[toc] | [prev] | [next] | [standalone]

#109048

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

[toc] | [prev] | [next] | [standalone]

#109049

"GS" <gs@v.invalid> wrote in message
> Yep, it's the last icon on the toolbar!

Which toolbar is that?

Peter T 

[toc] | [prev] | [next] | [standalone]

#109050

> "GS" <gs@v.invalid> 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

[toc] | [prev] | [next] | [standalone]

#109051

"GS" <gs@v.invalid> wrote in message
>> "GS" <gs@v.invalid> 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 

[toc] | [prev] | [next] | [standalone]

#109052

> "GS" <gs@v.invalid> wrote in message
>>> "GS" <gs@v.invalid> 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

[toc] | [prev] | [next] | [standalone]

#109040

"Tim Childs" <tsnip1NOTTHIS@yahoo.co.uk> wrote in message
> On 01-Jul-16 7:01 PM, Peter T wrote:
>> "Tim Childs" <tsnip1NOTTHIS@yahoo.co.uk> 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 

[toc] | [prev] | [next] | [standalone]

#109044

On 03-Jul-16 4:37 PM, Peter T wrote:
> "Tim Childs" <tsnip1NOTTHIS@yahoo.co.uk> wrote in message
>> On 01-Jul-16 7:01 PM, Peter T wrote:
>>> "Tim Childs" <tsnip1NOTTHIS@yahoo.co.uk> 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

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web