如何使用VBA更新切片器缓存 [英] How to update slicer cache with VBA

问题描述

我正在使用Excel VBA来隐藏/显示切片器上的元素，具体取决于用户的选择.

I'm using Excel VBA to hide/show elements on slicer depending upon user selection.

我有以下代码:

 Private Sub removeFilterWithSlicer()

 Dim slicerCache As slicerCache

 Set slicerCache = ThisWorkbook.SlicerCaches("Slicer_Channel1")

 slicerCache.SlicerItems("A").Selected = False
 slicerCache.SlicerItems("B").Selected = False
 slicerCache.SlicerItems("C").Selected = False
 slicerCache.SlicerItems("D").Selected = False
 slicerCache.SlicerItems("E").Selected = False
 slicerCache.SlicerItems("F").Selected = False

End Sub

其中，A，B等是切片器中元素的名称.我已经交叉检查了切片器缓存的名称("Slicer_Channel1").问题在于元素不会像预期的那样被取消选择.当我调试代码时，我发现每个元素都被一个一个地取消选择，但是当我到达过程的末尾(即End Sub)时，它们都回到了选择状态.

where A, B etc. are names of elements in slicer. I've cross checked the name of slicer cache ("Slicer_Channel1"). The issue is that the elements don't get deselected as they are supposed to. When I'm debugging the code, I found that each element gets deselected one by one but as soon as I reach the end of procedure i.e. End Sub, they all get back to being in selected state.

任何指针吗?

推荐答案

此代码显示了如何过滤名为vSelection的数组上的Slicer.

This code shows how to filter a Slicer on an array called vSelection.

Option Explicit

Sub FilterSlicer()
Dim slr As Slicer
Dim sc As SlicerCache
Dim si As SlicerItem
Dim i As Long
Dim vItem As Variant
Dim vSelection As Variant

Set sc = ActiveWorkbook.SlicerCaches("Slicer_ID")
'Set sc = slr.SlicerCache

vSelection = Array("B", "C", "E")

For Each pt In sc.PivotTables
    pt.ManualUpdate = True 'Stops PivotTable from refreshing after each PivotItem is changed
Next pt

With sc

    'At least one item must remain visible in the Slicer at all times, so make the first
    'item visible, and at the end of the routine, check if it actually  *should* be visible
    .SlicerItems(1).Selected = True

    'Hide any other items that aren't already hidden.
    'Note that it is far quicker to check the status than to change it.
    ' So only hide each item if it isn't already hidden
    For i = 2 To .SlicerItems.Count
        If .SlicerItems(i).Selected Then .SlicerItems(i).Selected = False
    Next i

    'Make the PivotItems of interest visible
    On Error Resume Next 'In case one of the items isn't found
    For Each vItem In vSelection
        .SlicerItems(vItem).Selected = True
    Next vItem
    On Error GoTo 0

    'Hide the first PivotItem, unless it is one of the countries of interest
    On Error Resume Next
    If InStr(UCase(Join(vSelection, "|")), UCase(.SlicerItems(1).Name)) = 0 Then .SlicerItems(1).Selected = False
    If Err.Number <> 0 Then
        .ClearAllFilters
        MsgBox Title:="No Items Found", Prompt:="None of the desired items was found in the Slicer, so I have cleared the filter"
    End If
    On Error GoTo 0
End With


For Each pt In sc.PivotTables
    pt.ManualUpdate = False
Next pt

End Sub

这篇关于如何使用VBA更新切片器缓存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！