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

W

whitehawk81

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

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
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
 
Upvote 0
Hi, AFAIK screen updating is automatically turned back on when the sub that disabled it terminates.
 
Upvote 0
Hi, but if I add Application.ScreenUpdating=FALSE at the beginning of the called sub, shouldn't that turn off the screen updating again?
 
Upvote 0
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.
 
Upvote 0
whitehawk81 said:
but if I add Application.ScreenUpdating=FALSE at the beginning of the called sub, shouldn't that turn off the screen updating again?
Click to expand...

Yes, but it's turned back on as soon as that called sub finishes, which is before anything else executes.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Changing slicer value
Replies
1
Views
553
sijpie
sijpie
sashapixie
  • Question Question
Synch slicers VBA - object defined error
Replies
3
Views
1K
DanteAmor
DanteAmor
J
  • Question Question
Run Time error 5
Replies
3
Views
1K
JPerkins26
J
L
  • Question Question
VBA Code to Sync Slicers
Replies
0
Views
1K
Langtn02
L
R
  • Question Question
VBA Multiple Slicers
Replies
0
Views
634
Rik76
R

Forum statistics

Threads
1,223,888
Messages
6,175,217
Members
452,619
Latest member
Shiv1198

Share this page

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