Application.ScreenUpdating=false not working when secondary sub is called

[whitehawk81]

Hi,
I got an annoying issue with Application.ScreenUpdating. It worked fine until I added a secondary subroutine into the code. I tried to look for a solution and already attempted to fix the issue by calling the following subs at the beginning and at the end of the primary and secondary subs as well:

Public Sub TU_Start()    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
End Sub


Public Sub TU_End()
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

This is the original code, that was working fine with Application.ScreenUpdating:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)Dim sc1 As SlicerCache, sc2 As SlicerCache, sc3 As SlicerCache, sc4 As SlicerCache, sc5 As SlicerCache, sc6 As SlicerCache, sc7 As SlicerCache, sc8 As SlicerCache, sc9 As SlicerCache
Dim si1 As SlicerItem, si3 As SlicerItem, si5 As SlicerItem, si7 As SlicerItem


Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Jahr")
Set sc2 = ThisWorkbook.SlicerCaches("Slicer_Jahr1")
Set sc3 = ThisWorkbook.SlicerCaches("Slicer_SolName")
Set sc4 = ThisWorkbook.SlicerCaches("Slicer_SolName1")
Set sc5 = ThisWorkbook.SlicerCaches("Slicer_Quarter")
Set sc6 = ThisWorkbook.SlicerCaches("Slicer_Quarter1")
Set sc7 = ThisWorkbook.SlicerCaches("Slicer_Monat")
Set sc8 = ThisWorkbook.SlicerCaches("Slicer_Monat1")
Set sc9 = ThisWorkbook.SlicerCaches("Slicer_Solution")


Application.ScreenUpdating = False
Application.EnableEvents = False




sc2.ClearManualFilter
sc4.ClearManualFilter
sc6.ClearManualFilter
sc8.ClearManualFilter
sc9.ClearManualFilter


On Error Resume Next


For Each si1 In sc1.SlicerItems
    sc2.VisibleSlicerItems(si1.Name).Selected = si1.Selected
Next si1


For Each si3 In sc3.SlicerItems
    sc4.VisibleSlicerItems(si3.Name).Selected = si3.Selected
    sc9.VisibleSlicerItems(si3.Name).Selected = si3.Selected
Next si3


For Each si5 In sc5.SlicerItems
    sc6.VisibleSlicerItems(si5.Name).Selected = si5.Selected
Next si5


For Each si7 In sc7.SlicerItems
    sc8.VisibleSlicerItems(si7.Name).Selected = si7.Selected
Next si7


On Error GoTo 0


clean_up:
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        'Exit Sub


err_handle:
        MsgBox Err.Description
        Resume clean_up


End Sub

And currently it looks like this:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)Dim sc1 As SlicerCache, sc2 As SlicerCache, sc3 As SlicerCache, sc4 As SlicerCache, sc5 As SlicerCache, sc6 As SlicerCache, sc7 As SlicerCache, sc8 As SlicerCache, sc9 As SlicerCache
Dim si1 As SlicerItem, si3 As SlicerItem, si5 As SlicerItem, si7 As SlicerItem


Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Jahr")
Set sc2 = ThisWorkbook.SlicerCaches("Slicer_Jahr1")
Set sc3 = ThisWorkbook.SlicerCaches("Slicer_SolName")
Set sc4 = ThisWorkbook.SlicerCaches("Slicer_SolName1")
Set sc5 = ThisWorkbook.SlicerCaches("Slicer_Quarter")
Set sc6 = ThisWorkbook.SlicerCaches("Slicer_Quarter1")
Set sc7 = ThisWorkbook.SlicerCaches("Slicer_Monat")
Set sc8 = ThisWorkbook.SlicerCaches("Slicer_Monat1")
Set sc9 = ThisWorkbook.SlicerCaches("Slicer_Solution")


Call TU_Start


sc2.ClearManualFilter
sc4.ClearManualFilter
sc6.ClearManualFilter
sc8.ClearManualFilter
sc9.ClearManualFilter


On Error Resume Next


For Each si1 In sc1.SlicerItems
    sc2.VisibleSlicerItems(si1.Name).Selected = si1.Selected
Next si1


For Each si3 In sc3.SlicerItems
    sc4.VisibleSlicerItems(si3.Name).Selected = si3.Selected
    sc9.VisibleSlicerItems(si3.Name).Selected = si3.Selected
Next si3


For Each si5 In sc5.SlicerItems
    sc6.VisibleSlicerItems(si5.Name).Selected = si5.Selected
Next si5


For Each si7 In sc7.SlicerItems
    sc8.VisibleSlicerItems(si7.Name).Selected = si7.Selected
Next si7


On Error GoTo 0


Call Filter_Columns("solH", "Dashboard", "Pivot", "SolPT", "Solution")
        
Call TU_End


End Sub

Is there a way to fix the application.screenupdating behaviour?

 

[whitehawk81]

I almost forgot, this is the secondary sub, which is called:

Sub Filter_Columns(sHeaderRange As String, sReportSheet As String, sPivotSheet As String, sPivotName As String, sPivotField As String)

Dim c As Range
Dim rCol As Range
Dim pi As PivotItem


Worksheets(sReportSheet).Range(sHeaderRange).EntireColumn.Hidden = False


Call TU_Start


For Each c In Worksheets(sReportSheet).Range(sHeaderRange).Cells


    With Worksheets(sPivotSheet).PivotTables(sPivotName).PivotFields(sPivotField)
        On Error Resume Next
        Set pi = .PivotItems(c.Value)
        On Error GoTo 0
    End With


    If Not pi Is Nothing Then
        If pi.Visible = False Then


            If rCol Is Nothing Then
                Set rCol = c
            Else
                Set rCol = Union(rCol, c)
            End If
        End If
    End If


    Set pi = Nothing


Next c


If Not rCol Is Nothing Then
    rCol.EntireColumn.Hidden = True
End If
Call TU_End
End Sub

 

[FormR]

Hi, AFAIK screen updating is automatically turned back on when the sub that disabled it terminates.

 

[whitehawk81]

Hi, but if I add Application.ScreenUpdating=FALSE at the beginning of the called sub, shouldn't that turn off the screen updating again?

 

[RoryA]

What precisely is the problem? Your second sub shouldn't call the routines to turn things on and off as they are already handled by the calling routine.

 

[FormR]

but if I add Application.ScreenUpdating=FALSE at the beginning of the called sub, shouldn't that turn off the screen updating again?

Yes, but it's turned back on as soon as that called sub finishes, which is before anything else executes.

 

[whitehawk81]

I'm a bit confused now. I modified the code and removed the two subroutines to change screen updating state from the secondary sub, but how can I turn the screen updating off again when the secondary sub is called?
I have 16 pivot tables that get updated, when the code runs and that causes flickering.

 

[FormR]

I'm a bit confused now.

Same here; I think it would help if you posted all the code as you now have it.

 

[whitehawk81]

This is the code, I have currently on the pivot sheet:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)Dim sc1 As SlicerCache, sc2 As SlicerCache, sc3 As SlicerCache, sc4 As SlicerCache, sc5 As SlicerCache, sc6 As SlicerCache, sc7 As SlicerCache, sc8 As SlicerCache, sc9 As SlicerCache
Dim si1 As SlicerItem, si3 As SlicerItem, si5 As SlicerItem, si7 As SlicerItem


Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Jahr")
Set sc2 = ThisWorkbook.SlicerCaches("Slicer_Jahr1")
Set sc3 = ThisWorkbook.SlicerCaches("Slicer_SolName")
Set sc4 = ThisWorkbook.SlicerCaches("Slicer_SolName1")
Set sc5 = ThisWorkbook.SlicerCaches("Slicer_Quarter")
Set sc6 = ThisWorkbook.SlicerCaches("Slicer_Quarter1")
Set sc7 = ThisWorkbook.SlicerCaches("Slicer_Monat")
Set sc8 = ThisWorkbook.SlicerCaches("Slicer_Monat1")
Set sc9 = ThisWorkbook.SlicerCaches("Slicer_Solution")


Call TU_Start


sc2.ClearManualFilter
sc4.ClearManualFilter
sc6.ClearManualFilter
sc8.ClearManualFilter
sc9.ClearManualFilter


On Error Resume Next


For Each si1 In sc1.SlicerItems
    sc2.VisibleSlicerItems(si1.Name).Selected = si1.Selected
Next si1


For Each si3 In sc3.SlicerItems
    sc4.VisibleSlicerItems(si3.Name).Selected = si3.Selected
    sc9.VisibleSlicerItems(si3.Name).Selected = si3.Selected
Next si3


For Each si5 In sc5.SlicerItems
    sc6.VisibleSlicerItems(si5.Name).Selected = si5.Selected
Next si5


For Each si7 In sc7.SlicerItems
    sc8.VisibleSlicerItems(si7.Name).Selected = si7.Selected
Next si7


On Error GoTo 0


Call Filter_Columns("solH", "Dashboard", "Pivot", "SolPT", "Solution")


Application.ScreenUpdating = False
        
Call TU_End


End Sub

This code gets called to filter the table columns based on the slicer selection:

Sub Filter_Columns(sHeaderRange As String, sReportSheet As String, sPivotSheet As String, sPivotName As String, sPivotField As String)

Dim c As Range
Dim rCol As Range
Dim pi As PivotItem


Worksheets(sReportSheet).Range(sHeaderRange).EntireColumn.Hidden = False


For Each c In Worksheets(sReportSheet).Range(sHeaderRange).Cells


    With Worksheets(sPivotSheet).PivotTables(sPivotName).PivotFields(sPivotField)
        On Error Resume Next
        Set pi = .PivotItems(c.Value)
        On Error GoTo 0
    End With


    If Not pi Is Nothing Then
        If pi.Visible = False Then


            If rCol Is Nothing Then
                Set rCol = c
            Else
                Set rCol = Union(rCol, c)
            End If
        End If
    End If


    Set pi = Nothing


Next c


If Not rCol Is Nothing Then
    rCol.EntireColumn.Hidden = True
End If


End Sub

And these subs control the screen updating and the events:

Public Sub TU_Start()    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
End Sub


Public Sub TU_End()
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[FormR]

Hi, I think that you should turn off and on screen updating in the Worksheet_PivotTableUpdate event and only in the Worksheet_PivotTableUpdate event.