VBA to output MANUALLY slicer-selected items ONLY, in a specified cell?

[ajjava]

I've gotten SOOO close to a solution, but something invariably falls apart in practice (and I don't think it's because this is a complicated procedure...instead, it's because I know enough VBA to speak (somewhat) intelligently about it, and to write basic procedures, but I fall apart when it comes to writing logical and efficient procedures that require many loops and logical tests).

Use case:

• One pivot table (non-data model pivot)
• Many slicers
• Desired output: All manually-selected slicer items (to be used in the title of related charts)
  • Output on a specified sheet
  • Can be in a list or in a comma-delimited string
• Criteria:
  • Only return values from slicers that have been manually filtered (so those with the red x next to the filter icon)
    • IGNORE any slicers that have been filtered/limited only as a result of another manually-filtered slicer
  • IGNORE the filtering out of '(blank)'items, even though they may be filtered manually by the user
    • i.e. If the only item that's been filtered out is '(blank)', treat that slicer as NOT FILTERED and don't return any output
  • Loop through all slicers dynamically, no specific slicer names used in the script

I would post my cobbled-together, unreliable code, but I fear that it will just unnecessarily "lead the witness" and complicate matters.

Any help would be immensely appreciated, as I've burned HOURS on getting this to work.

Here is a visual:

 

[Jeffrey Mahoney]

Please try this. You'll need to substitute the slicer name and the range output for your use. To find the slicer name, start recording a macro, select items in the slicer and stop the macro recording

Sub GetSelSlicerNames()
  Dim SI As SlicerItem
  Dim SINames As String

  For Each SI In ActiveWorkbook.SlicerCaches("Slicer_PO").SlicerItems
    If SI.Selected = True Then
      If SINames <> "" Then
        SINames = SINames & ", " & SI.Name
      Else
        SINames = SI.Name
      End If
    End If
  Next SI
  Range("K1") = SINames
    
End Sub

 

[ajjava]

Any way to address these two criteria elements?

Meanwhile, I'll go and give your initial procedure a try...thanks!

 

[ajjava]

Any way to address these two criteria elements?

View attachment 100922

Meanwhile, I'll go and give your initial procedure a try...thanks!

I see you DID address the selection of blanks....sorry, I missed it!

 

[Jeffrey Mahoney]

OK, this is going to output values in cells J1:K??

Sub GetSelSlicerNames()
  Dim Slcr As SlicerCache
  Dim SI As SlicerItem
  Dim SINames As String
  Dim SIName As String
  Dim BlankSel As Boolean
  Dim AllSINames As String
  Dim X As Long

  For Each Slcr In ActiveWorkbook.SlicerCaches
    Range("J1").Offset(X, 0).Value = Slcr.Name
    For Each SI In Slcr.SlicerItems                             'ActiveWorkbook.SlicerCaches("Slicer_PO")
      SIName = SI.Name
      If SIName <> "(blank)" Then
        If AllSINames <> "" And SIName <> "(blank)" Then
            AllSINames = AllSINames & ", " & SIName
          Else
            AllSINames = SIName
        End If
      End If
      If SI.Selected = True Then
        If SI.Name = "(blank)" Then
          BlankSel = True
        Else
          If SINames <> "" Then
            SINames = SINames & ", " & SI.Name
          Else
            SINames = SI.Name
          End If
        End If
      End If
    Next SI
    If BlankSel = True And SINames = "" Then
      Range("K1").Offset(X, 0) = AllSINames
    Else
      Range("K1").Offset(X, 0) = SINames
    End If
    X = X + 1
    SINames = ""
    AllSINames = ""
    BlankSel = False
  Next Slcr
    
End Sub

 

[Jeffrey Mahoney]

In that version above, if the user ONLY selected blank for a slicer, it returns all the items. Did you want it to return nothing?

 

[Jeffrey Mahoney]

Do you want to only return slicer values for the activesheet or the whole workbook?

 

[ajjava]

Do you want to only return slicer values for the activesheet or the whole workbook?

As it stands now, there will only be one pivot and one set of associated slicers
And for the (blank) items - If the user selects ONLY (blank), then NOTHING should be returned (but I'm not overly concerned about this scenario)
The more important element related to (blanks) items is if that's the only item that's filtered OUT by the slicer. In that case, I want the procedure to treat that slicer as if it has NOTHING selected.

I also added the following to your initial procedure (the bit in red font):

For Each SI In ActiveWorkbook.SlicerCaches("Slicer_Account_Name").SlicerItems
If SI.Selected = True And SI.HasData Then

I'm about to go and test your revised code. I am VERY grateful for your assistance

 

[ajjava]

I just tested the revised code.
For any slicer that has no manual selection made (i.e. there is no RED X at the top of the slicer), there should be NO OUTPUT.

Here's a visual of my results

Here are the slicers. For my output, I would've expected the ONLY output to be the selected value for 'Slicer_Literal' (aka 'Claim Status')
I would've expected NO output for 'WC Claim Type Upd...', since the only filtering done was to EXCLUDE (blank)

Here is the output

 

[ajjava]

And one more SUPER annoying thing that's been happening, both with my feeble coding attempts and with your procedure - values from the prior data source are remaining in some of the slicers - no matter what I've tried to stop it. The items highlighted in red, within the red outlined boxes, are all from the previous data source.
I've tried:

1. Clearing the slicer cache
2. Changing the settings in 'Slicer Settings' (toggled 'hide items with no data', 'show items deleted from the data source', etc.)

Any ideas on suppressing that behavior?