CODEWITHSUNDEEP
vbaexcel
DevRenanGaspar
DevRenanGaspar

Reputation: 373

VBA Editor flashes during Macro run

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:

  1. Open another excel workbook

  2. Copy over the first sheet from this workbook to my current workbook

  3. Create a button in the copied sheet

  4. 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!

Upvotes: 0

Views: 446

Answers (1)

Maddy
Maddy

Reputation: 781

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:

  1. Cpearson - Eliminating Screen Flicker During VBProject Code

  2. MSDN- VBE flashes while programming in the VBE

Upvotes: 2

Related Questions