excelvbavbe

How to stop Visual Basic editor from automatically opening after macro runs?


I have working code where the only issue is that it opens the Visual Basic editor while it's running, closes it, then reopens it when it's finished.

I believe this is because I am creating workbooks, importing a module, and adding a sheet event to each one before closing it. This is when the Visual Basic editor will pop open, then close.

After it finishes all this, the editor will open again and remain open.

Is there some way to either stop the editor from opening or to close the window once it all finishes?

My last resort solution will be to just close the workbook once it finishes running.

ScreenUpdating and DisplayAlerts are disabled for the duration of the macro.

Sub MasterCopy()

Dim file_name As String
file_name = Dir(personal_path)
Dim wb_sel As Workbook

Do While file_name <> ""
    If Not IsFileOpen(personal_path & file_name) Then ''''function checking if file is open
        
        Set wb_sel = Workbooks.Add(master_path & master_name)
        
        Call add_code(wb_sel)
        
        wb_sel.SaveAs filename:=personal_path & file_name, FileFormat:=xlExcel12, ReadOnlyRecommended:=True
        wb_sel.Close (False)
    Else
        FailedUpdate.Add file_name '''this is a collection
    End If
    
    file_name = Dir()
Loop

End Sub

Sub add_code(ByVal wb_sel As Workbook)

Dim xLine As Integer
Dim xMod As VBIDE.CodeModule
Set xMod = wb_sel.VBProject.VBComponents("Sheet1").CodeModule
    
wb_sel.VBProject.VBComponents.Import (code_import) ''''imported module

With xMod
    xLine = .createeventproc("Change", "Worksheet")
    xLine = xLine + 1
    .insertlines xLine, "application.screenupdating = false" & vbLf & "Call rng_change(Target)" & vbLf & "application.screenupdating = true" ''''adding sheet event
End With

End Sub

Solution

  • Application.VBE.MainWindow.Visible = False

    Was the solution. Tacked on to end, closes VBE window after it all finishes.