VBA - Application.ScreenUpdate - Becoming "True" at some point?

[goodysgotacuda]

I have a code that runs for 5-20 seconds depending on how much data I import. With that imported data, I switch between sheets within the same workbook several times.

From what I have found, there are different methods for calling other sheets, some of which cause the Application.ScreenUpdate to change its value. I am using the following code to change sheets:

Sheets("Temperature").Select

When I execute the macro, the ScreenUpdate = False works, then I see the "Temperature" sheet until the code ends. However, there are other Sheets("").select within the macro that doesn't seem to affect it.

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
            
    'Unhide All Sheets
    For Each ws In ActiveWorkbook.Worksheets
    ws.Visible = xlSheetVisible
    Next ws
    ActiveSheet.Shapes("Arrow").Visible = False
    
    
    Sheets("Collected Data").Select

           
            Set Pete1 = Application.FileDialog(msoFileDialogFilePicker)
            With Pete1
                .InitialView = msoFileDialogViewDetails
                .InitialFileName = ThisWorkbook.Path
                .Filters.Add "Open File ", "*.txt", 1
                .ButtonName = "Import file"
                .Title = " Select .txt File for Import - Data"
                If .Show = -1 Then
                    FullPath = .SelectedItems(1)
            Else:
                    Sheets("Home").Select
                    Exit Sub
                End If
            End With
            With ActiveSheet.QueryTables.Add(Connection:= _
                "TEXT;" & FullPath, Destination:=Range("A1"))
            
...............
                End With
                    
            Selection.AutoFilter
            Selection.Columns.AutoFit
            With Selection
...............
            End With
            
            Range("A1").Select
            Range("C1:E1").Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.Copy
            Range("A1").Select
            
            Sheets("Temperature").Select
            
            Range("E1").Select
            ActiveSheet.Paste
            Range(Selection, Selection.End(xlDown)).Select
                Application.ScreenUpdating = False
                'Air Temp
                   
                Application.Run "ATPVBAEN.XLAM!Sample", ActiveSheet.Range("$E$2:$E$999999") _
               , ActiveSheet.Range("$B$2"), "P", 6, False

I believe the error is somewhere within the red area, but I cannot find it!

 

[par60056]

Application.screenupdating=False speeds up execution by not showing what is going on. Generally you want to turn it to false at the start of a long macro and back to true just before the end.

I think the line Range("A1").select will clear the copy mode.

Most macros can be written without changing the selection.

I think the equivalent code would look something like:

        Range("A1").Select
        Range("C1:E1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Range("A1").Select
        
        Sheets("Temperature").Select
        
        Range("E1").Select
        ActiveSheet.Paste

Range("C1:E1").End(xlDown).Copy Sheets("Temperature").Cells(1, 5)
'need to activate the destination since the next line uses ActiveSheet
Sheets("Temperature").activate

 

[goodysgotacuda]

Thank you for the input.


I am setting the screenupdating to false and am still seeing "whats going on" at certain parts of the code. I was hoping to resolve that issue.

 

[par60056]

If I were to guess, I would bet that displaying the filedialog turns it back on. Otherwise they would not be able to see the dialog.

you could use debug.print to find where it changes.

Debug.Print "before dialog ", Application.ScreenUpdating
Set Pete1 = Application.FileDialog(msoFileDialogFilePicker)
Debug.Print "after dialog ", Application.ScreenUpdating

 

[goodysgotacuda]

I am finally getting back to this problem and have been able to use the "watch" function to see where my status is changing. My Application.ScreenUpdating changes from False to True once the Application.Run passes.

                                                                                    'Air Temp
         Application.Run "ATPVBAEN.XLAM!Sample", ActiveSheet.Range _
         ("$E$2:$E$999999"), ActiveSheet.Range("$B$2"), "P", 6, False
                                                                                    'Fuel Temp
         Application.Run "ATPVBAEN.XLAM!Sample", ActiveSheet.Range _
         ("$F$2:$F$999999"), ActiveSheet.Range("$C$2"), "P", 6, False
                                                                                    'Coolant Temp
         Application.Run "ATPVBAEN.XLAM!Sample", ActiveSheet.Range _
         ("$G$2:$G$999999"), ActiveSheet.Range("$D$2"), "P", 6, False

Any suggestions on how to change that? I am using a sampling data function here.

 

[RoryA]

If it gets turned back on by the add-in, you only really have two choices I can think of:
1. Keep turning it back off again.
2. Use the LockWindowUpdate API call to stop the screen redrawing.

 

[goodysgotacuda]

If it gets turned back on by the add-in, you only really have two choices I can think of:
1. Keep turning it back off again.
2. Use the LockWindowUpdate API call to stop the screen redrawing.

I turn it off right after that section runs, but still it has already changed sheets to one I don't particualrly want users staring at while it runs.
I will look into the LockWindowUpdate. Thanks

 

[RoryA]

For example:

Declare Function LockWindowUpdate Lib "user32" (ByVal hwndLock As Long) As Long

Sub foo()
    LockWindowUpdate Application.Hwnd
    'your code

    'make sure this always gets called!
    LockWindowUpdate 0&
End Sub

 

[goodysgotacuda]

Thank you Rory.

I inputted that into the sub I am currently experiencing the issue in and received the "Sub or Function not Defined" complie error. Any suggestions? Do I need to declare it as a function?

 

[RoryA]

Did you put the 'Declare Function..' line at the top of the module?