• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

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

You are here: Home / Automatically Create Slicers|VBA & Macros|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosAutomatically Create Slicers
sp_PrintTopic sp_TopicIcon
Automatically Create Slicers
Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
1
June 24, 2022 - 6:21 am
sp_Permalink sp_Print

Hi there. In the attached XLS I try to automatically create a set of slicers by looping through the pivot table columns (and creating one for each column I want to slicer).

I'm running into an "Invalid Procedure Call or Argument" error and can't figure out what I'm doing wrong.

The offending line is the "Add2" method:

Dim wb As Workbook
Dim sc As SlicerCache
Set wb = ThisWorkbook
Set sc = wb.SlicerCaches.Add2(mySource, mySourceField) ' <---- ERROR 5

At the time of error:

  • mySource = "MyPT" (the name of the pivot table on the active sheet)
  • mySourceField = "COUNTRY" (the header of the first column of the pivot table)

Thanks for any guidance!  

sp_AnswersTopicSeeAnswer See Answer
Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
2
June 24, 2022 - 6:49 pm
sp_Permalink sp_Print

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

Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
3
June 24, 2022 - 10:20 pm
sp_Permalink sp_Print

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?

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
4
June 27, 2022 - 5:07 pm
sp_Permalink sp_Print

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.

Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
5
June 27, 2022 - 10:37 pm
sp_Permalink sp_Print sp_EditHistory

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
Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
6
June 27, 2022 - 11:11 pm
sp_Permalink sp_Print

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)

Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
7
June 28, 2022 - 7:16 am
sp_Permalink sp_Print

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

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
8
June 28, 2022 - 6:03 pm
sp_Permalink sp_Print

That is the line where you needed the Set:

 

Set myPTSettings.ObjRef = pto

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
9
June 28, 2022 - 6:06 pm
sp_Permalink sp_Print

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.
Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
10
June 29, 2022 - 5:11 am
sp_Permalink sp_Print

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.

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
11
June 29, 2022 - 5:28 pm
sp_Permalink sp_Print

This is still unnecessary:

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

slc already is the slicer you want.

sp_AnswersTopicAnswer
Answers Post
Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
12
June 30, 2022 - 4:04 am
sp_Permalink sp_Print

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.

 

   

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
13
June 30, 2022 - 5:49 pm
sp_Permalink sp_Print

Your workbook didn't attach...

Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
14
June 30, 2022 - 10:09 pm
sp_Permalink sp_Print

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.

Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
15
June 30, 2022 - 10:14 pm
sp_Permalink sp_Print

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

Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
16
June 30, 2022 - 10:25 pm
sp_Permalink sp_Print

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

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
17
July 1, 2022 - 10:00 pm
sp_Permalink sp_Print

The method seems quite temperamental about its argument types. I'd suggest:

 

Dim pf As PivotField
For Each pf In ptObject.RowFields

' Create slicer cache and slicer for selected PT fields
Select Case pf.Caption
Case "ART", "TEAM", "PI", "SPRINT", "VALID", "WARNING"

strSourceField = pf.CubeField.Name
strSlcrName = pf.Caption
strSlcrCaption = pf.Caption

Set rng = Range("Q6:T6")
lSpace = lSpace + 160

' The empty param is the OLAP hierarchy level, which is N/A

Set slcr = wb.SlicerCaches.Add2( _
Source:=CVar(ptObject), _
SourceField:=CVar(strSourceField)).Slicers.Add( _
SlicerDestination:=ws.Name, _
Name:=ws.Name & " " & pf.Caption, _
Caption:=pf.Caption, _
Top:=rng.Top, Left:=50, Width:=150, Height:=120)

'With slcr
' .NumberOfColumns = 3
' .RowHeight = 13
' .ColumnWidth = 70
'End With
Case Else
' do nothing

End Select
Next pf

End Sub

 

and for your reset code it looks like you might as well just loop through all the slicercaches and delete them (that should delete the associated slicers at the same time).

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Malcolm Sawyer
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 216
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Melanie Ford
Isaac Felbah
Adele Glover
Hitesh Asrani
Rohan Abraham
Anthony van Riessen
Erlinda Eloriaga
Abisola Ogundele
MARTYN STERRY
Rahim Lakhani
Forum Stats:
Groups: 3
Forums: 24
Topics: 6355
Posts: 27792

 

Member Stats:
Guest Posters: 49
Members: 32324
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.