Close All Open Windows in the Visual Basic Editor SOLVED, but I still have a question

[bernatbosch]

Until today I was working with Visual Basic on a project and it turns out that this project had a routine which always left a window of the Visual Basic Editor as visible in the Visual Basic editor (corresponding to a standard code module that is created during the same routine).

As this always forced me to manually hide this particular window in the Visual Basic editor every single time I ran my code (and this was not good because it ended up being a highly repetitive task) I decided to study how could my code could leave that window of the newly created code module hidden in the Visual Basic Editor.


After spending the evening trying without much success, now what happens is even worse: every time I run the code (a routine which opens a bunch of workbooks located in a folder on my Desktop folder) not only this window remains visible in my Visual Basic Editor, but Excel now shows up ALL THE WINDOWS OF ALL THE CODE MODULE PANES OF ALL THE BOOKS OPEN BY THIS FILE (unless the book project is password protected, which is the case of my book Personal macros, only time that I could see that the windows are not displayed when opening the book).


Now I have a code to close all open windows in the Visual Basic Editor open except for the one of the VB Project explorer, code that I developed myself with the time I have been able to study the matter and it works great, but there is still a problem: only serves me right when I run it manually from the Visual Basic editor, and it seems useless when trying to make it run from an event of the workbook I'm working with.

This is my code for hiding all open windows in the VB editor:

Private Sub CLOSE_AllOpenVBEWindows()
ThisWorkbook.VBProject.VBE.ActiveWindow.Visible = True
'1. loop through the workbooks collection (avoiding ThisWorkbook) and through their VBE windows collections to hide all VBE open windows
For Each Wk In Application.Workbooks
If Not Wk.Name = ThisWorkbook.Name Then
For Each CodeWindow In Wk.VBProject.VBE.Windows
If Not CodeWindow.Caption = "Project" Then
If CodeWindow.Visible = True Then CodeWindow.Visible = False
End If
Next CodeWindow
End If
Next Wk
'2. loop through the VBE windows collection of ThisWorkbook and hide all open VBE windows but the active one
For Each CodeWindow In ThisWorkbook.VBProject.VBE.Windows
If Not CodeWindow.Caption = ThisWorkbook.VBProject.VBE.ActiveWindow.Caption Then
If Not CodeWindow.Caption = "Project" Then
If CodeWindow.Visible = True Then CodeWindow.Visible = False
End If
End If
Next CodeWindow
'3. close the active window
ThisWorkbook.VBProject.VBE.ActiveWindow.Visible = False
'4. open the VB Project Explorer window back again
ThisWorkbook.VBProject.VBE.Windows("Project").Visible = True
End Sub

As I mentioned above, this code works great.

Nevertheless, something seems to be out of my range of understanding when I try to execute the code automatically through the workbook open event of this particular workbook file. It looks like this file has some setting which make the execution fail when linked to the workbook open event.

Also, It looks like this effect of uncontrolled expanding of all the code module windows in the VBE is strictly linked with this single workbook file, the one from which I was testing and developing my code for that issue today.

I've tried to open other workbook files and they behave differently (they don't show or 'pop-up' all the code module windows in the VBE when you open them).

Could be that my workbook file got somehow corrupted?

be nice!

 

[Norie]

I'm sorry but that code is really hard to follow, could you repost it using code tags so it's kind of indented.

 

[bernatbosch]

Sorry, I'm self-made in the vba coding, as you can see... besides, english is not my mother language.

However, can you be more explicit with what do you or what is it understood by code tags? I mean, even if some patterns in code tagging seem to be shared between different developers, I have seen a lot of different ways to write the same concepts.

In brief, can you give me a hand, some taggs list...? I did check the web link above and seems its a post on HTML taggs, isn't it? Actually, I'm using VBA, so maybe there are collections of taggs for VBA?

thanks

 

[bernatbosch]

Private Sub CLOSE_AllOpenVBEWindows()

ThisWorkbook.VBProject.VBE.ActiveWindow.Visible = True

'1. loop through the workbooks collection (avoiding ThisWorkbook) and through their VBE windows collections to hide all VBE open windows

For Each Wk In Application.Workbooks
If Not Wk.Name = ThisWorkbook.Name Then
For Each CodeWindow In Wk.VBProject.VBE.Windows
If Not CodeWindow.Caption = "Project" Then
If CodeWindow.Visible = True Then CodeWindow.Visible = False
End If
Next CodeWindow
End If
Next Wk

'2. loop through the VBE windows collection of ThisWorkbook and hide all open VBE windows but the active one

For Each CodeWindow In ThisWorkbook.VBProject.VBE.Windows
If Not CodeWindow.Caption = ThisWorkbook.VBProject.VBE.ActiveWindow.Caption Then
If Not CodeWindow.Caption = "Project" Then
If CodeWindow.Visible = True Then CodeWindow.Visible = False
End If
End If
Next CodeWindow

'3. close the active window

ThisWorkbook.VBProject.VBE.ActiveWindow.Visible = False

'4. open the <acronym title="vBulletin" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VB</acronym> Project Explorer window back again

ThisWorkbook.VBProject.VBE.Windows("Project").Visible = True

End Sub

 

[Norie]

Check my signature.

 

[bernatbosch]

Actually, the step number 4 is not necessary anymore, as I added the code needed to avoid the closing of the VBE Project Explorer window in each of the two loops made in steps 1. (line 4) and step 2. (line 3).


So the procedure would be something like that:


Private Sub CLOSE_AllOpenVBEWindows()

ThisWorkbook.VBProject.VBE.ActiveWindow.Visible = True

'1. loop through the workbooks collection (avoiding ThisWorkbook) and through their VBE windows collections to hide all VBE open windows

For Each Wk In Application.Workbooks
If Not Wk.Name = ThisWorkbook.Name Then
For Each CodeWindow In Wk.VBProject.VBE.Windows
If Not CodeWindow.Caption = "Project" Then
If CodeWindow.Visible = True Then CodeWindow.Visible = False
End If
Next CodeWindow
End If
Next Wk

'2. loop through the VBE windows collection of ThisWorkbook and hide all open VBE windows but the active one

For Each CodeWindow In ThisWorkbook.VBProject.VBE.Windows
If Not CodeWindow.Caption = ThisWorkbook.VBProject.VBE.ActiveWindow.Caption Then
If Not CodeWindow.Caption = "Project" Then
If CodeWindow.Visible = True Then CodeWindow.Visible = False
End If
End If
Next CodeWindow

'3. close the active window

ThisWorkbook.VBProject.VBE.ActiveWindow.Visible = False

End Sub

 

[bernatbosch]

By the way, I was trying to migrate the whole code of the workbook file which gave me the issue and, well, its seems to work like heaven in the brand new file.

So I assume I did change some settings of the original workbook that caused the problem... or the original file got corrupted somehow...!

About your signature, it brings me to a post about HTML tags, but I can't tell how to use them in VBA (?). Do I miss something, here…?