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]

Hi, so now I modified the pivot update sub and turned screen updating and events off and back only there. The table filter sub is still called before the screen updating is enabled again, but when I change the slicer filter, the pivot charts still flicker.

 

[whitehawk81]

Now I also checked the screen updating with Debug.Print Application.ScreenUpdating, but it always stays FALSE.

 

[FormR]

Maybe it's one of the things that doesn't get supressed by turning screen updating off. It's difficult to test at this end without the workbook.

 

[whitehawk81]

I created a sample file and uploaded it here.

 

[whitehawk81]

Sorry, I left the other modules there. The pivot update sub is on the pivot sheet and the table filter sub is in module 5.

 

[FormR]

Hi, I downloaded the workbook and the only flickering that I see happens before the event is fired.

 

[whitehawk81]

I just tested the workbook with Excel 2013 and I don't get any flickering. So the issue only persists in Excel 2010.

 

[Jaafar Tribak]

See if this helps .. Remove all Application.ScreenUpdating and modify your code with the changes in RED as follows :

[COLOR=#ff0000]#If VBA7 Then
    Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
    Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
    Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If

Private Const WM_SETREDRAW = &HB[/COLOR]
    
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")


[COLOR=#ff0000]SendMessage FindWindowEx(FindWindowEx(Application.hwnd, 0, "XLDESK", vbNullString), 0, "EXCEL7", vbNullString), ByVal WM_SETREDRAW, 0, 0[/COLOR]

[COLOR=#008000]'Application.ScreenUpdating = False[/COLOR]
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
       
Call Filter_Columns("solH", "Dashboard", "Pivot", "SolPT", "Solution")

'clean_up:

[COLOR=#ff0000]SendMessage FindWindowEx(FindWindowEx(Application.hwnd, 0, "XLDESK", vbNullString), 0, "EXCEL7", vbNullString), ByVal WM_SETREDRAW, 1, 0[/COLOR]

        Application.EnableEvents = True
[COLOR=#008000]'        Application.ScreenUpdating = True[/COLOR]

'err_handle:
        'MsgBox Err.Description
        'Resume clean_up
End Sub
Private Sub CommandButton1_Click()
Call ListPivotsInfor
End Sub

 

[whitehawk81]

Hi, thanks for the advice. It fixed the flickering issue, but the table gets refreshed only, when the pivot update code runs again.

 

[whitehawk81]

I just found out, what was causing the flickering. In the called table update sub the code was using two different sheets (dashboard and pivot sheets) at the same time. As soon as I moved the table to the pivot sheet, the flickering disappeared.