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

goodysgotacuda

Board Regular
Joined
Jun 27, 2014
Messages
51
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:
Rich (BB code):
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.

Rich (BB code):
    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!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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:
Code:
        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

Code:
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.

Rich (BB code):
                                                                                    '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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
For example:
Code:
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
 
Upvote 0
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?
 
Upvote 0
Did you put the 'Declare Function..' line at the top of the module?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top