blocks|key|2420508|text|您可以使用For+Each循环遍历SlicerCache中的所有SlicerItems：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2420509|Dim+si+as+SlicerItem

For+Each+si+In+ActiveWorkbook.SlicerCaches("Slicer_Organization").SlicerItems
++++si.Selected+=+(si.Name+=+"900110+-+Danish+Bemidji")
Next+si|code-block|syntax|javascript|2420510|该(si.Name+=+"900110+-+Danish+Bemidji")将返回一个True或False，并相应地设置您正在迭代的SlicerItem的Selected属性。|2420511|entityMap^0|5|8|H|B|W|B|0|0|1|11|17|4|1C|5|1U|A|25|8|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@$9|X|A|Y|B|C]|$9|Z|A|10|B|C]|$9|11|A|12|B|C]|$9|13|A|14|B|C]|$9|15|A|16|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|17|8|@]|D|@]|E|$]]]|N|$]]

You can loop through all <code>SlicerItems</code> in your <code>SlicerCache</code> using a <code>For Each</code> loop:

<pre><code>Dim si as SlicerItem

For Each si In ActiveWorkbook.SlicerCaches("Slicer_Organization").SlicerItems
 si.Selected = (si.Name = "900110 - Danish Bemidji")
Next si
</code></pre>

That <code>(si.Name = "900110 - Danish Bemidji")</code> will return a <code>True</code> or <code>False</code> and set the <code>Selected</code> property of the <code>SlicerItem</code> you are iterating appropriately.

blocks|key|2074949|text|过滤SlicerItems可能很棘手，因为至少有一项必须始终保持可见。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2074950|此代码显示如何筛选名为vSelection的数组上的切片器。要根据需要进行修改，只需更改vSelection+=+Array("B",+"C",+"E")行，使其包含感兴趣的项目。|offset|length|style|CODE|2074951|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")

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,+"%7C")),+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|code-block|syntax|javascript|2074952|entityMap^0|0|18|X|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Filtering SlicerItems can be tricky, because at least one item must remain visible at all times. 

This code shows how to filter a Slicer on an array called vSelection. To amend to your needs, just change the <code>vSelection = Array("B", "C", "E")</code> line so that it contains the item(s) of interest.

<pre><code>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")

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 &lt;&gt; 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
</code></pre>

blocks|key|2533462|text|Sub+ExcelGuruVG()
++Dim+myval+As+String
++myval+=+"VG"
++Dim+sli+as+SlicerItem

++with+Activeworkbook.slicercaches("Slicer_SLICERNAME")
++++for+Each+sli+in+SlicerItems
++++++if+sli.Name+=+myval+then
++++++++sli.select+=+true
++++++Else
++++++++sli.selected+=+False
++++++End+if
++++Next+sli
++End+with
End+Sub|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2533463|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>Sub ExcelGuruVG()
 Dim myval As String
 myval = &quot;VG&quot;
 Dim sli as SlicerItem

 with Activeworkbook.slicercaches(&quot;Slicer_SLICERNAME&quot;)
 for Each sli in SlicerItems
 if sli.Name = myval then
 sli.select = true
 Else
 sli.selected = False
 End if
 Next sli
 End with
End Sub
</code></pre>

Trying to narrow down this VBA script w/o having to list out . I'm a basic Macro user. How can I get one selected = True and the rest = False without having to list all 160?

<pre><code>With ActiveWorkbook.SlicerCaches("Slicer_Organization")
 .SlicerItems("900110 - Danish Bemidji ").Selected = True
 .SlicerItems("900101 - Arabic Bemidji").Selected = False
 .SlicerItems("900105 - Chinese Callaway 1st Half").Selected = False
 .SlicerItems("900106 - Chinese Callaway 2nd Half").Selected = False
 .SlicerItems("900115 - Finnish Bemidji ").Selected = False
 .SlicerItems("900120 - French Bemidji 1st Half ").Selected = False
 .SlicerItems("900121 - French Bemidji 2nd Half ").Selected = False
</code></pre>

How to select one slicer = true and remaining = false in VBA

正在尝试缩小此VBA脚本的范围，但不必列出。我是一个基本的宏用户。我如何才能让一个selected = True，rest = False，而不必列出所有160个？With ActiveWorkbook.SlicerCaches("Slicer_Organization")    .SlicerItems("90011...

问如何在VBA中选择一个切片器= true和剩余= false
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在VBA中选择一个切片器= true和剩余= falseEN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在VBA中选择一个切片器= true和剩余= false
EN