Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to prevent window flickering/switching in Excel?

Tags:

excel

vba

I am copying from one file to an other file, and I can see Excel switching between the source and the target file (like flickering). I want the macro to copy from the source and paste to target WITHOUT switching between the files (I DON'T want flickering).

So here I got my Excel VBA code. I set up a Button to run 2 macros. The first macro is an openfiledialog where I can choose a file to be opened. That is required because the needed file always got a different name and is in a different path.

After opening the file my second macro starts (CopyPasteValues), the one I posted below. At first I set my source and target Workbooks/Worksheets and create an array with 16 account numbers.

Then I use the find method to search for the account number in each file (source and target). The result of the find method is used to create an offset in the src file and copy it to an offset in the target file.

Sub CopyPasteValues()
    Dim srcWb As Workbook    'source Wb
    Dim srcWs As Worksheet   'source Ws
    Dim trgWb As Workbook    'target Wb
    Dim trgWs As Worksheet   'target Ws

    Set trgWb = ActiveWorkbook
    Set trgWs = trgWb.Sheets("Entry Sheet 20004100")
    Set srcWb = Workbooks.Open(Filename:=openedFile, UpdateLinks:=False, ReadOnly:=True, Editable:=False)
    Set srcWs = srcWb.Sheets("20004100") 

    Dim GLAccountField
    'Array of 16 Account numbers
    GLAccountField = Array(430000, 446030, 477030, 474210, 446075, 472700, 472710, 476000, 476100, 476610, 452200, 454700, 471300, 473110, 490000, 490710)

    Dim srcFinder As Range, trgFinder As Range
    Dim searchGL As Long
    Dim srcRng As Range, trgRng As Range
    Dim i As Integer

    For i = LBound(GLAccountField) To UBound(GLAccountField)

        'The range where GL Accounts will be searched
        Set srcRng = srcWs.Range("A1:A100")    'source file
        Set trgRng = trgWs.Range("B10:B900")   'target file

        'search for the account number(i) in source and target sheets
        searchGL = GLAccountField(i) 
        Set srcFinder = srcRng.Find(searchGL, Lookat:=xlWhole, LookIn:=xlValues, MatchCase:=True)
        Set trgFinder = trgRng.Find(searchGL, Lookat:=xlWhole, LookIn:=xlValues, MatchCase:=True)


        'If finder value equals searched Account Number, then paste to target
        If srcFinder Is Nothing Then
            MsgBox "GL Account: " & searchGL & " NOT found in 'Accounting Input' file"
        Else
            'copy from source
            srcFinder.Offset(0, 15).Resize(1, 12).Copy
            'paste to target from source
            trgFinder.Offset(1, 4).Resize(1, 12).PasteSpecial xlPasteValues  

        End If
    Next i
    srcWb.Close
End Sub
like image 435
mafraqs Avatar asked Jun 04 '14 17:06

mafraqs


People also ask

Why does Excel keep glitching?

If you're experiencing high CPU usage, Excel might crash due to insufficient resources. This usually happens if you've added animations to your spreadsheet. To fix it, you should disable hardware graphics acceleration. Besides keeping Excel from freezing or crashing, it will improve the overall performance.


1 Answers

(Answered in comments by Andy G):

Use Application.ScreenUpdating = False at the start of your sub. Remember to set it back to True at the end (also good practice to do this in an error handler so it gets reset even in the event of an error):

Sub foo()
  On Error Goto errHandler
  Application.ScreenUpdating = False

  'Your code here

  Application.ScreenUpdating = True
errHandler:
  Application.ScreenUpdating = True
End Sub
like image 159
aucuparia Avatar answered Nov 14 '22 22:11

aucuparia