December 7, 2021
I have a macro called "ResetReport" that deletes all slicers on a sheet and then adds back (and formats) four specific slicers to the same sheet. This is essentially a mechanism to "reset" a report to the way it was originally setup. The last step in the procedure connects the new slicers to the table that they will control. Everything up to and including that point works fine.
What I'm trying to do next is de-select all slicer items except the first so that the chart does not show an overwhelming amount of information (i.e., all options from all slicers). The code I've written (some borrowed, some original) causes an error.
Sub ResetReport ' The example I'm putting here simplifies things for purposes of this question 'Call DeleteAllSlicers ' Remove all existing slicers 'Call AddDefaultSlicers ' Add 2-4 slicers, depending on the active sheet from which this sub is called ' Note that the slicers created in this routine are given the following names ' Slicer #1: ActiveSheet.Name & Chr(32) & "ART" ' Slicer #2: ActiveSheet.Name & Chr(32) & "TEAM" ' Slicer #3: ActiveSheet.Name & Chr(32) & "PI" ' Slicer #4: ActiveSheet.Name & Chr(32) & "SPRINT" 'Call SetSlicerProperties ' Formats the new slicers (header, caption, position, size, etc.) 'Call ConnectSlicersToTable ' Connects the slicers to the table on the sheet Dim strSlicerName As String strSlicerName = ActiveSheet.Name & Chr(32) & "ART" ' I'm hard coding this here so you can see what's passed Call SliceByIndex(strSlicerName) ' This is the sub that's erroring out End Sub
Sub SliceByIndex(strSlicerName As String)
Debug.Print "START SliceByIndex (" & strSlicerName & ")" ' This line prints, so we get this far
' Declare variables for this procedure Dim sc As SlicerCache Dim si As SlicerItem Dim lSlicerItemsCount As Long Dim n As Long MsgBox "The name of the slicer is: " & strSlicerName ' This shows the correct slicer name ' Set variable values Set sc = ThisWorkbook.SlicerCaches(strSlicerName) ' This line triggers Err 5: "Invalid procedure call or argument" ' Count slicer items lSlicerItemsCount = sc.SlicerItems.Count ' We never get this far Debug.Print "Number of slicer items: " & lSlicerItemsCount With sc ' Temporarily suspend PivotCache recalculation Debug.Print "Suspend PivotCache recalculation..." .PivotTables(1).ManualUpdate = True ' Select first slicer item (required) Debug.Print "Select first slicer item..." .SlicerItems(1).Selected = True ' Deselect all otehr slicer items For n = 2 To lSlicerItemsCount Debug.Print "Deselect slicer item: " & n & "..." .SlicerItems(n).Selected = False Next n
' Resume PivotCache recalculation
Debug.Print "Resume PivotCache recalculation..."
.PivotTables(1).ManualUpdate = False
' Skip over error handler
' Display error
Msgbox ("SliceByIndex", Err.Number, Err.Description, strSlicerName)
debug.print "END: SliceByIndex"
November 1, 2018
December 7, 2021
Thanks, as always. I'll continue to investigate, but...
The message box toward the top of the SliceByIndex sub displays this message: "The name of the slicer is: ReportVelocity ART." And when I look at the slicer, I see this:
So, I gather there is a difference between the "Name" property (which I'm using, and which is causing the error) and the "Name to use in formulas" property (which you're suggesting I need to use instead).
If so, then how can I retrieve the "Name to use in formulas" property for a slicer that I just created using VBA? How do I know whether it's Slicer_ART1 or Slicer_2 or Slicer_ART3 or whatever?
November 1, 2018
More importantly, there is a difference between a SlicerCache and a Slicer.
The SlicerCache name is "Slicer_" and the name of the Source field (I don't know if that prefix varies depending on language). Since you are creating the slicers in your code, you can refer to the SlicerCache property of each one directly to manipulate that.