You need to pass a pivot table object, not its name.

Thanks. I understand the issue now, but I'm not sure why my update is causing run-time error 91: "Object variable or With block variable not set"

The new object variable "pto" (i.e., the pivot table object I need to pass to the slicer) has been declared as type Object and I use the Set statement to define it.

What am I missing here?

It's an object variable so you need to use Set when assigning it. You should really declare it as a PivotTable in the UDT though rather than a generic object.

Thanks, Velouria.

The following code works, but it seems unnecessarily complicated. Thoughts?

CODE IN WORKSHEET
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
  Const MySlicer As String = "Slicer_TEAM3"
  Call LimitToOne(Target, MySlicer)
End Sub


CODE IN MODULE
Sub LimitToOne(ByVal rgTargetRange As PivotTable, strSlicerName As String)
' Checks whether a specified slicer has more than one item selected.
' If so, the last action is undone

On Error GoTo ERROR_HANDLER

Application.EnableEvents = 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 triggerd by any other pivot operation

' Check undo stack to verify event was triggered by slicer or filter
Select Case sLastUndoStackItem
Case "Slicer Operation", "Filter"
Case vbNullString: GoTo GOODBYE ' Undo stack empty
Case Else: GoTo GOODBYE
End Select

' Get slicer index
Dim myIndex As Long
For Each slc In ActiveWorkbook.SlicerCaches
If slc.Name = strSlicerName Then myIndex = slc.Index: Exit For
Next slc

' Validate slicer exists
On Error Resume Next
Set slc = ActiveWorkbook.SlicerCaches(myIndex)
On Error GoTo 0
If slc Is Nothing Then GoTo GOODBYE

' 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 Not bSlicerIsConnected Then GoTo GOODBYE

' Loop through cach levels then slicer items to count selected items
For Each slcrCL In ActiveWorkbook.SlicerCaches(myIndex).SlicerCacheLevels
For Each si In slcrCL.SlicerItems
If si.Selected Then iSelected = iSelected + 1
If iSelected > 1 Then Exit For
Next
Next

If iSelected = 1 Then GoTo GOODBYE

MsgBox "Only one team may be selected at a time in this report."
With Application
.EnableEvents = False
.Undo
End With

GoTo GOODBYE
ERROR_HANDLER:

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

GOODBYE:

Application.EnableEvents = True

End Sub

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)

My apologies. I confused two questions I had about the same sample workbook.

The code I pasted in my last comment above works to limit the slicer to one selection at a time. I feel like it's inelegant, but it seems to work. But that was not my original question in this thread, so let me get back to that...

The challenge I'm trying to solve in this thread is actually about CREATING the slicers in the first place. When the "Auto Create Slicers" button is clicked, it should loop through the pivot table headers and create a slicer for each one.

I followed your direction (I think):

1. Declared the object variable
2. Assigned a valid reference to the object variable using the Set statement (Set pto = "ws.PivotTables(1))

I must be missing something, because the code still throws Error 91 on this line: myPTSettings.ObjRef = pto

That is the line where you needed the Set:

Set myPTSettings.ObjRef = pto

Just a quick note re your other code:

1. Goto statements are generally best avoided (other than error handling). If you feel you need one, 99% of the time it means your code needs restructuring and/or refactoring.

2. This seems a bit "round the houses":

' Get slicer index
Dim myIndex As Long
For Each slc In ActiveWorkbook.SlicerCaches
If slc.Name = strSlicerName Then myIndex = slc.Index: Exit For
Next slc

' Validate slicer exists
On Error Resume Next
Set slc = ActiveWorkbook.SlicerCaches(myIndex)
On Error GoTo 0
If slc Is Nothing Then GoTo GOODBYE


You're storing the index of a slicercache so that later you can use the index to get a reference to the same slicercache. Just store the reference to the object in your first loop.

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.

This is still unnecessary:

If slc.Name = strSlicerName Then Set slc = ActiveWorkbook.SlicerCaches(slc.Index): Exit For

slc already is the slicer you want.

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.

Your workbook didn't attach...

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.

P.S. - If prompted, the password is my last name.