CODEWITHSUNDEEP
excelvba
il_betto
il_betto

Reputation: 21

How to close windows in VBA Editor with VBA code

I have a file excel and I ' d like that if the user go inside VBA Editor, he can see it but without the windows inside it opened (as default)

I have used the code below but when I run macro nothing happens and, when I go to VBA Editor I continue to see opened the windows ... I don' t understand why ...

Thanks in advance !!!

Sub cc()

Dim wk, CodeWindow

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

End Sub

Thanks a lot in advance !!

Upvotes: 0

Views: 4117

Answers (2)

il_betto
il_betto

Reputation: 21

Thanks a lot, a lot for the help !!

The macro, assembled like this, for me is good !!

Public Sub CloseAllVBEWindows()

Dim wk As Workbook
Dim CodeWindow As Variant

Application.VBE.MainWindow.Visible = True

For Each wk In Application.Workbooks
    If wk.Name = ThisWorkbook.Name Then
       For Each CodeWindow In wk.VBProject.VBE.Windows
           If CodeWindow.Visible = True Then CodeWindow.Visible = False
       Next CodeWindow
    End If
Next wk

End Sub

Fantastic !!

My problem was that as Option of Excel I have no put flag inside the last line of the image attached: "trust access to the VBA project object model"enter image description here

Is there a way, with VBA code, to flag in automatic this Excel option ?

Thanks a lot again !!

Upvotes: 1

Tsiry Rakotonirina
Tsiry Rakotonirina

Reputation: 731

This , I think, is the Function you need to Close automatically all the open VBE Windows:

Public Sub CloseAllVBEWindows()
Dim wk As Workbook
Dim CodeWindow As Variant

'If VBE is closed the user shall allow it to open first
If Application.VBE.MainWindow.Visible = False Then
    If MsgBox("VBE is still closed!" & vbCrLf & "The operation requires VBE to be open during its process. Would you like to open it?", vbInformation + vbYesNo) = vbYes Then
        Application.VBE.MainWindow.Visible = True
    Else
        Exit Sub
    End If
End If

'Then it will close all the windows
'Except the Default VBE Windows (Immediate Windows, Locals Windows ...)
For Each wk In Application.Workbooks
    If wk.Name = ThisWorkbook.Name Then
       For Each CodeWindow In wk.VBProject.VBE.Windows
           If CodeWindow.Visible = True And CodeWindow.Type = 0 Then CodeWindow.Visible = False
       Next CodeWindow
    End If
Next wk

End Sub

You need to understand that in VBE even all the Panels are listed, so you shall close only the CodeWindow.Type = 0 (Visual Basic Script Windows).

And it shall solve your issue! Just let me know

Update: If VBE is closed the it will open VBE first and proceed to the Closing

Upvotes: 2

Related Questions