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

whitehawk81

Board Regular
Joined
Sep 4, 2016
Messages
52
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:

Code:
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:
Code:
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:
Code:
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?
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

whitehawk81

Board Regular
Joined
Sep 4, 2016
Messages
52
I almost forgot, this is the secondary sub, which is called:
Code:
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
Hi, AFAIK screen updating is automatically turned back on when the sub that disabled it terminates.
 

whitehawk81

Board Regular
Joined
Sep 4, 2016
Messages
52
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,073
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 4, 2016
Messages
52

ADVERTISEMENT

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.
 

whitehawk81

Board Regular
Joined
Sep 4, 2016
Messages
52
This is the code, I have currently on the pivot sheet:
Code:
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:
Code:
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:
Code:
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
Office Version
  1. 365
Platform
  1. Windows
Hi, I think that you should turn off and on screen updating in the Worksheet_PivotTableUpdate event and only in the Worksheet_PivotTableUpdate event.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,631
Messages
5,597,282
Members
414,134
Latest member
Snowfall

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
Top