Application.ScreenUpdating automatically changes to True

[pecosvil]

I have a code that initially sets ScreenUpdating to False, but in the middle of the code it calls another subroutine and for some reason it actually shows me some (not all) actions (ie, when it selects a new sheet I get to see that). I have been following ScreenUpdating Property through Debug.Print Application.ScreenUpdating and it always stays as FALSE.

Here is a simplified version of the code

Option Explicit


Sub Check_InputData()


Application.ScreenUpdating = False
Application.EnableEvents = False

Worksheets("Instructions").Calculate
Worksheets("Lists").Calculate
Call DP_disbursement
Worksheets("DP").Calculate 

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

The subroutine DP_disbursement is located in a different module (is that relevant?). The code is more or less the following:

Sub DP_disbursement()

Dim number_disbursements As Integer
Application.EnableEvents = False


Worksheets("DP").Unprotect
Worksheets("DP").Select
ActiveSheet.Calculate
Range("B43:C72").Copy


Range("D43").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("D43").Select
    ActiveSheet.Calculate
Range("A1").Select
Worksheets("DP").Protect


Application.EnableEvents = True 


End Sub

Anyone ideas? Thanks in advance!

 

[shg]

Try replacing the latter sub with this:

Sub DP_disbursement()
    Application.EnableEvents = False
    
    With Worksheets("DP")
        .Unprotect
        .Calculate
        With .Range("B43:C72")
            .Range("D43").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
        .Calculate
        .Protect
    End With
    Application.EnableEvents = True
End Sub

 

[JoeMo]

Try to avoid selecting objects - a good general principle. Not sure, but in your called routine the selecting may be what triggers a screen update. Try this version to see if it helps:

Sub DP_disbursement()

Dim number_disbursements As Integer
Application.EnableEvents = False


Worksheets("DP").Unprotect
Worksheets("DP").Calculate
Range("B43:C72").Copy


Range("D43").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
'    Range("D43").Select
    ActiveSheet.Calculate
'Range("A1").Select
Worksheets("DP").Protect


Application.EnableEvents = True


End Sub

 

[pecosvil]

Thanks to both. Indeed, I changed the code to avoid using .Select and .Activate, and it worked fine. So the problem is solved.
Still, any ideas why the ScreenUpdating = False doesn't work when I select a different sheet? Isn't it suppose to work exactly for that?

Is it because the selection was performed in a different sub? in a different module? and why does the Debug.Print Application.ScreenUpdating didn't catch any change in the ScreenUpdating Property?

 

[JoeMo]

Does the debug.print reside in the main sub or the called sub?

 

[pecosvil]

in both. I put a debug.print before calling the sub. And then put a debug.print before/after each line (including before after the select line). and then put a debug print after the called sub ends and the code returns to the original sub. in all cases it was FALSE.

 

[JoeMo]

in both. I put a debug.print before calling the sub. And then put a debug.print before/after each line (including before after the select line). and then put a debug print after the called sub ends and the code returns to the original sub. in all cases it was FALSE.

What happens if using your original sub with the selects in it you add Application.ScreenUpdating = false at its start?

 

[pecosvil]

That was the first thing I tried. I was still experiencing the same problem...

 

[JoeMo]

That was the first thing I tried. I was still experiencing the same problem...

Well that narrows it down to the selecting I guess, and I guess further that it's specifically the selection of a sheet that wasn't the active sheet when you launched the macro. A selected sheet comes to the top even though screen updating is off. So, the debug.prints are correct, reflecting the fact that screen updating is off.