Where to place Application.ScreenUpdating function to stop flicker

[redspanna]

No matter where I put the Application.Screenupdating function in this code, it still flickers when running,

Where should it be placed to avoid the 'flicker'

Sub CopyEbayData()

   
    Dim sourceWorkbook As Workbook
    Dim targetWorkbook As Workbook
    Dim sourceWorksheet As Worksheet
    Dim copiedWorksheet As Worksheet
   
   Application.ScreenUpdating = False
   
    ' Set the source workbook (workbook with "ebay-orders" in its name)
    Set sourceWorkbook = GetWorkbookWithMatchingName("ebay-Listings")
    
    ' Check if the source workbook was found
    If Not sourceWorkbook Is Nothing Then
        ' Set the source worksheet (the only worksheet in the source workbook)
        Set sourceWorksheet = sourceWorkbook.Worksheets(1)
        
        ' Set the target workbook (workbook named "Sales4")
        Set targetWorkbook = ThisWorkbook ' Assuming this code is in the Sales4 workbook
        
        ' Copy the source worksheet to the target workbook
        sourceWorksheet.Copy After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count)
        
        ' Set the copied worksheet object reference
        Set copiedWorksheet = targetWorkbook.Sheets(targetWorkbook.Sheets.Count)
        
        ' Rename the copied worksheet to "ebay"
        copiedWorksheet.Name = "ebay"
        
         
        ' Clean up
        Set sourceWorksheet = Nothing
        sourceWorkbook.Close False ' Close the source workbook without saving changes
        Set sourceWorkbook = Nothing
        
        'run code to add VLOOKUP formula
        'If_string_match_found_place_formula
        'run code to change neagative numbers to positive
        'ChangeNegativeValues
        
  Application.ScreenUpdating = True
        
    Else
        MsgBox "Source workbook not found."
        
       
    End If
    
End Sub

Thanks in advance

 

[rlv01]

It isn't a brief flicker, the updates last for a few seconds which I really don't want to have to see
any ideas where to insert the Application.screenupdating to stop it?

So a couple of things:

1. In terms of 'where do I place it', you have to first identify where in the code the flicker occurs. I usually do this by placing temporary End statements, first near the beginning of the code and then running the macro, and then moving End to a new location, depending on what I find out about where the flicker happens.

2. If is lasting a few seconds, you may also need to place some DoEvents statements just before the code that causes the flicker.

There is no one single answer. You have to experiment.

 

[redspanna]

OK, Thanks all - I'll try those options

 

[Jaafar Tribak]

I sometimes use an API based alternative to the native Excel ScreenUpdating Property. Not sure if this will help but you can give it a shot.

In a Standard Module:

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
    Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
#Else
    Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
    Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
#End If

Public Property Let ScreenUpdatingAPI(ByVal vNewValue As Boolean)
    Const WM_SETREDRAW = &HB
    #If Win64 Then
        Dim hwnd As LongLong
    #Else
        Dim hwnd As Long
    #End If
    hwnd = FindWindowEx(Application.hwnd, 0, "XLDESK", vbNullString)
    hwnd = FindWindowEx(hwnd, 0, "EXCEL7", vbNullString)
    Call SendMessage(hwnd, ByVal WM_SETREDRAW, ByVal vNewValue, 0)
    If vNewValue Then Application.ScreenUpdating = True
End Property

Code Usage example:

Sub Test()

    ScreenUpdatingAPI = False
  
        ' run your code here ...
  
    ScreenUpdatingAPI = True

End Sub