Report Slicer Selections (Skip if ALL are selected) using VBA

[trixits]

Hi Gurus,


I need help with some VBA code. I have an AgeRange slicer and I have a working script that inserts a row, adds a timestamp, and then reports the slicer selections.


I'd like to add something to this that will SKIP the process if ALL the items in the slicer are selected (True).


Is there something that I can insert that says "If the slicer hasn't been touched (all items are true), then end sub".


Here's what I have for code so far:

[pre]

Dim cache As Excel.SlicerCache
Set cache = ActiveWorkbook.SlicerCaches("Slicer_AgeRange")
Dim sItem As Excel.SlicerItem
For Each sItem In cache.SlicerItems
If sItem.Selected = True Then xAge = xAge & sItem.Name & ", "
Next sItem
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = Format(Now(), "MM-DD-YYYY HH:MM AM/PM")
Range("B1").Select
ActiveCell.FormulaR1C1 = xAge
Range("C1").Select
End Sub

[/pre]

Any help is greatly appreciated!

 

[SirJB7]

Hi, trixits!

Any chance uploading a sample file?

Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!

 

[trixits]

Hi SirJB7,


Thanks for the response. Its a little cumbersome to get it out of my XLSM at the moment.

I will work to get one posted, or definitely if I get a working answer.


The question at hand should be answerable without the file, I think.

 

[trixits]

Hi Gurus,


I'd still be interested in hearing any insights on this, if possible, without the file.

 

[trixits]

Nvm, I got it!

[pre]

Dim cache As Excel.SlicerCache
Dim sName As Slicers
Dim sItem As Excel.SlicerItem
Dim xSlice As String
Dim xName As String

For Each cache In ActiveWorkbook.SlicerCaches

xName = StrConv(Replace(Replace(Replace(Replace(Replace(Replace(cache.Name, "AgeRange", "Ages"), "PurchaseDay", "Day of Month"), "__Retention", ""), "__Monetization", ""), "Slicer_", ""), "_", " "), vbProperCase)
xCheck = 0
For Each sItem In cache.SlicerItems
If sItem.Selected = False Then
xCheck = xCheck + 1
Else
xCheck = xCheck
End If
Next sItem

If xCheck > 0 Then
For Each sItem In cache.SlicerItems
If sItem.Selected = True Then
xSlice = xSlice & sItem.Caption & ", "
End If
Next sItem

Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
ActiveCell.FormulaR1C1 = xName & ": " & xSlice
xSlice = ""
End If

Next cache

Range("A1").Select
ActiveCell.FormulaR1C1 = Format(Now(), "MM-DD-YYYY HH:MM AM/PM")

End Sub

[/pre]

 

[SirJB7]

Hi, trixits!

You could too perform this check:

-----

[pre]

If cache.SlicerItems.Count = cache.VisibleSlicerItems.Count Then
DoSomething1
Else
DoSomething2
End If

[/pre]
-----

This condition tests if any slicer has a selected filter value and avoids performing the loop thru all items, that depending on how many of them are might increase performance too, besides easier code reading.

BTW, thank you for sharing your solution.

Regards!