VBA Editor flashes during Macro run

Question

after a lot of research I couldn't find anyone with the same problem as me. So can any of the gurus please help me with my Excel Macro?

My macro does the following:

Open another excel workbook
Copy over the first sheet from this workbook to my current workbook
Create a button in the copied sheet
Write some code in this new created button

And here is the problem, when my macro writes the code in the button, it opens the VBA Code Editor and closes afterwards. My macro does it many times, so the VBA Code Editor keeps flashing during the macro run.

"Application.ScreenUpdating = False" didn't resolve the issue.

Please see below my code to do this Step 4 and let me know if you know a solution for that.

wb is my Workbook and ws my Worksheet

 Set oOleObj = ws.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=5.4, Top:=4.8, Width:=97.2, Height:=35.4)

Set VBP = wb.VBProject
Set VBC = VBP.VBComponents(VBP.VBComponents.Count)
Set CM = VBC.CodeModule

With wb.VBProject.VBComponents(wb.Worksheets(ws.Name).CodeName).CodeModule
    LineNum = .CreateEventProc("click", oOleObj.Name)
    LineNum = LineNum + 1
    .InsertLines LineNum, "UploadToAlmButton_OnClick"
End With

I could simple protect the project from viewing with a password. That should resolve the issue, but creates another one: If it's protected, I cannot write code on it by macro as I am doing in the Step 4. :(

Thanks!

I do not think the IDE window needs to be open. For a user, if the IDE window is never opened it will not repaint. The IDE window is not actually created until a developer presses Alt-F11. So no need to worry, I think. — S Meaden
I copied my excel with the macro to another computer to check this, but unfortunately it opened the IDE window anyway. Thanks :( — DevRenanGaspar
Maddy's answer is good, so I'd persist with that, if that fails then you could copy from one you made earlier instead of always creating on the fly. — S Meaden

Maddy Maddy · Accepted Answer · 2018-03-15T10:45:05

To hide VBE window

Application.VBE.MainWindow.Visible = False
Application.VBE.MainWindow.Visible = True

If VBE window is still flickering then you need to use LockWindowUpdate Windows API function.

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal ClassName As String, ByVal WindowName As String) As Long

Private Declare Function LockWindowUpdate Lib "user32" _
    (ByVal hWndLock As Long) As Long


Sub EliminateScreenFlicker()
    Dim VBEHwnd As Long

    On Error GoTo ErrH:

    Application.VBE.MainWindow.Visible = False

    VBEHwnd = FindWindow("wndclass_desked_gsk", _
        Application.VBE.MainWindow.Caption)

    If VBEHwnd Then
        LockWindowUpdate VBEHwnd
    End If

    '''''''''''''''''''''''''
    ' your code here
    '''''''''''''''''''''''''

    Application.VBE.MainWindow.Visible = False
ErrH:
    LockWindowUpdate 0&
End Sub

References:

VBA Editor flashes during Macro run

1 Answers