CODEWITHSUNDEEP
vbaslicers
Kathy
Kathy

Reputation: 53

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

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?

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

Upvotes: 2

Views: 10466

Answers (4)

Zaira
Zaira

Reputation: 1

I tried the code that was shared by Venugopal and combined it with JNevill's code, and it worked! Thanks a bunch to both of you! The adjustment I made is as below:

Sub ExcelGuruVG()
  Dim myval As String
  myval = ThisWorkbook.Worksheets("Sheet1").Range("A2").Value  'Taking the value from Sheet1 in Cell A2

  Dim sli As SlicerItem
  With ActiveWorkbook.SlicerCaches("Slicer_SlicerName1")
  For Each sli In ActiveWorkbook.SlicerCaches("Slicer_SlicerName1").SlicerItems
      If sli.Name = myval Then
        sli.Selected = True
      Else
        sli.Selected = False
      End If
    Next sli
  End With
End Sub

Upvotes: 0

Venugopal K R
Venugopal K R

Reputation: 11

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

Upvotes: 1

jeffreyweir
jeffreyweir

Reputation: 4824

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 vSelection = Array("B", "C", "E") line so that it contains the item(s) of interest.

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 <> 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

Upvotes: 3

JNevill
JNevill

Reputation: 50064

You can loop through all SlicerItems in your SlicerCache using a For Each loop:

Dim si as SlicerItem

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

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

Upvotes: 1

Related Questions