Automatically Create Slicers|VBA & Macros|Excel Forum|My Online Training Hub

P.S. - It occurs to me that the simplest solution, albeit the most fragile in case of structural changes to the worksheet, might just be:

If Application.CountA(Range("X:X")) > 2 Then MsgBox "BOOM!" ' If there is more than a header and one value in the column then trigger the error (and then undo)

With respect to the GoTo statements... this is what I ended up with (just in case anyone out there in Internetland finds it useful). Notice a "gotcha" at the bottom. 

Sub LimitToOne(ByVal rgTargetRange As PivotTable, strSlicerName As String, ByVal strFocus As String)
' Limit slicer to one selection

On Error GoTo ERROR_HANDLER

Application.EnableEvents = False
Application.ScreenUpdating = False

Dim bSlicerIsConnected As Boolean
Dim pvt As PivotTable
Dim slc As SlicerCache
Dim sLastUndoStackItem As String

sLastUndoStackItem = Application.CommandBars("Standard").FindControl(ID:=128).List(1)

' Continue only if event was triggered by slicer or filter
' Exit if triggered by any other pivot operation

' Check undo stack to verify event was triggered by slicer or filter
Select Case sLastUndoStackItem
Case vbNullString ' Undo stack empty; do nothing
Case "Slicer Operation", "Filter" ' Continue processing

' Get slicer index
For Each slc In ActiveWorkbook.SlicerCaches
If slc.Name = strSlicerName Then Set slc = ActiveWorkbook.SlicerCaches(slc.Index): Exit For
Next slc

If Not slc Is Nothing Then

' Validate that the pivot table that triggered the event is connected to specified slicer
For Each pvt In slc.PivotTables
If pvt.Name = rgTargetRange.Name Then bSlicerIsConnected = True: Exit For
Next pvt

' Check how many slicer items are selected
' Cancel if more than one

Dim slcrCL As SlicerCacheLevel
Dim si As SlicerItem
Dim iSelected As Integer

If bSlicerIsConnected Then
For Each slcrCL In ActiveWorkbook.SlicerCaches(slc.Index).SlicerCacheLevels
For Each si In slcrCL.SlicerItems
If si.Selected Then iSelected = iSelected + 1
Next
Next

If iSelected > 1 Then
Dim vbMsg As VbMsgBoxResult
vbMsg = MsgBox("It is recommended that this report be limited to one " & strFocus & _
" at a time (you currently have " & iSelected & " selected)." & _
vbNewLine & vbNewLine & _
"Would you liked to undo the previous action?", _
vbQuestion, vbYesNo, vbDefaultButton1, "Set Scope")
If vbMsg = vbYes Then
With Application
.EnableEvents = False
.Undo
End With
End If
End If
End If
End If

Case Else ' A trigger other than slicer or filter; do nothing
End Select

GoTo GOODBYE

ERROR_HANDLER:

MsgBox Err.Number & " | " & Err.Description

GOODBYE:

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

GOTCHA

I have to UNCHECK "Hide items with no data" in the COUNTRY slicer. Here's why. The choice of COUNTRY (slicer 1) determines the selection the STATES (slicer 2). Once I force the user to select exactly one STATE, then the COUNTRY slicer is immediately filtered to show only the country to which that state belongs. I can never pick a different country.

With respect to the AutoCreateSlicers proc, I submitted a Help Desk ticket on that one.

Thank you, Velouria. I made that update.

I'm attaching a sample XLS which illustrates that I'm still struggling with slicer caches and slicers, despite (I think) following your directions.  The XLS has several sheets, but the key ones are the data entry sheet ("Enter Velocity") and the report sheet ("Rpt Forecast Sheet"). The report uses the entered data.

If you click the huge "Click Here First" command button (on the report sheet) a userform will appear (if it is not already visible). That userform then has a command button called "Reset/Refresh." Clicking that button triggers a procedure called "UniversalResetWrapper."

The UniversalResetWrapper is does some simple UI clean-up work which is not relevant here (and which works), so I won't describe it. The key step in the wrapper proc is a call to a separate proc called "ResetSlicers." The ResetSlicers proc primarily does the following:

1. Determines whether there is a pivot table on the sheet
2. If yes, then it determines which slicers are need, depending on which specific pivot table was found (e.g., ART, TEAM, PI, SPRINT, VALID, and/or WARNING)
3. Deletes all current slicers (I do this to kind of start from scratch, so to speak. Maybe there is a less destructive way?)
4. Calls "NEW_AutoCreateSlicers" to create the appropriate set of new slicers <--- THIS IS WHERE THE PROBLEM IS
5. Filters the new slicers to select only one ART or TEAM slicer item
6. Connects the slicers to the pivot table

The NEW_AutoCreateSlicers procedure is supposed to loop through the headers of whatever pivot table is on the sheet and (if the header matches one the target headers - ART, TEAM, PI, SPRINT, VALID, or WARNING) then create a slicer for that pivot table column.

You'll see two attempts at making this work in the modSlicers module:

1. NEW_AutoCreateSlicers
2. OLD_AutoCreateSlicers

Anyway,  the following line triggers Error 5 "Invalid procedure call or argument":

Set slcr = wb.SlicerCaches.Add2(ptObject, strSourceField).Slicers.Add(ws, , strSlcrName, strSlcrCaption, rng.Top, rng.Left + lSpace, 150, 120)

I thought I had correctly declared the object variable and used SET to assign the object variable to an object.

I'm sorry if I've just misunderstood or misapplied previous guidance, but what am I missing?

Thanks.

Ugh. I always miss that error about file size. I deleted all the irrelevant forms, etc. and zipped the file. Now it's small enough and attached. Thanks.

By the way, this recorded macro works, but I don't want to use it. I'm trying not to do this much hardcoding of values. That's why my procedure is trying to be modestly clever about this. Also, if I run this twice, I get an error that the slicer cache already exists. 

wb.SlicerCaches.Add2( _
ws.PivotTables("PT_Velocity_Forecast"), _
"[Table_Velocity].[ART]").Slicers.Add _
ws, _
"[Table_Velocity].[ART].[ART]", _
"ART", _
"ART", _
169.5, 816, 144, 155.5