Hi Shawn,

The slicer does have a NumberOfColumns property:

The problem is that you're working with a Shape object, not with a Slicer object:

Set myShape = ActiveSheet.Shapes("Catchup Slicer ART") 'ART slicer

myShape.NumberOfColumns = 2

There is a place in code where you actually refer to a slicer object:
mySlicer.DisableMoveResizeUI = LockFlag

This is the object where you can apply a numberof columns property.

Instead of working with a shape, I suggest working with the slicer:

Set MySlicer=ThisWorkbook.SlicerCaches("Catchup Slicer ART")

A Slicer object has even a Shape property that you can use to set shape properties:
Set MyShape=MySlicer.Shape
myShape.Left = Application.InchesToPoints(10.4) 'horizontal
myShape.Top = Application.InchesToPoints(1.1) 'vertical
myShape.Height = Application.InchesToPoints(1) 'height
myShape.Width = Application.InchesToPoints(2.5) 'width

I'd use something like this in the worksheet:

Private Sub Worksheet_Activate() ' this code is replicated in multiple sheets

On Error Resume Next
Application.ScreenUpdating = False

'Unlock slicer positions
Call SlicerLockPosition(Me, False)

'Position the pivot chart slicers
Dim FormatSettings As SlicerFormatSettings

' Specify settings
With FormatSettings
.Left = Application.InchesToPoints(10.4)
.Top = Application.InchesToPoints(1.1)
.Height = Application.InchesToPoints(1)
.Width = Application.InchesToPoints(2.5)
.NumberOfColumns = 2
.RowHeight = 18.72
.ColumnWidth = 82.08
End With
' apply settings to slicer
FormatSlicer "Catchup Slicer ART", FormatSettings

' change any settings that are different
With FormatSettings
.Top = Application.InchesToPoints(2.15)
.Height = Application.InchesToPoints(1.85)
End With
' apply settings to next slicer
FormatSlicer "Catchup Slicer TEAM", FormatSettings

With FormatSettings
.Top = Application.InchesToPoints(4.05)
.Height = Application.InchesToPoints(1)
End With

FormatSlicer "Catchup Slicer PI", FormatSettings

'Format Slicer - Iteration
FormatSettings.Top = Application.InchesToPoints(5.1) 'vertical
FormatSlicer "Catchup Slicer Sprint", FormatSettings

'Format Slicer - Status
With FormatSettings
.Top = Application.InchesToPoints(0.35) 'vertical
.Height = Application.InchesToPoints(0.4) 'height
End With
FormatSlicer "Catchup Slicer Status", FormatSettings

'Lock slicer positions
Call SlicerLockPosition(Me, True)

Application.ScreenUpdating = True

End Sub

------------------------------------------

and then in a normal module:

Option Explicit
Type SlicerFormatSettings
Left As Single
Top As Single
Height As Single
Width As Single
NumberOfColumns As Long
RowHeight As Double
ColumnWidth As Double
End Type
Sub SlicerLockPosition(ws As Worksheet, LockFlag As Boolean)

Dim myShape As Shape

Dim mySlicer As Slicer

For Each myShape In ws.Shapes 'Loop through all slicers on sheet
If myShape.Type = msoSlicer Then
Set mySlicer = GetSlicer(myShape.Name)
If Not mySlicer Is Nothing Then
mySlicer.DisableMoveResizeUI = LockFlag
End If
End If
Next myShape

End Sub
Sub FormatSlicer(SlicerName As String, SlicerFormat As SlicerFormatSettings)

Dim theSlicer As Slicer
Set theSlicer = GetSlicer(SlicerName)

If Not theSlicer Is Nothing Then
With theSlicer
.NumberOfColumns = SlicerFormat.NumberOfColumns
.RowHeight = SlicerFormat.RowHeight
.ColumnWidth = SlicerFormat.ColumnWidth
With .Shape
.Left = SlicerFormat.Left
.Top = SlicerFormat.Top
.Height = SlicerFormat.Height
.Width = SlicerFormat.Width
End With
End With
End If
End Sub

It looks like you already have a GetSlicer function so I haven't included mine. 😉 I used a UDT partly because you seem to want to pass quite a lot of parameters, and partly because it makes it easier to reuse the same values between slicers as you seem to be doing (you only need to alter the ones that differ for each slicer after the initial settings are made).

Thank you, Velouria and Catalin. This is great. I learned a lot. Heree's what I ended up with...

Private Sub Worksheet_Activate()

On Error Resume Next
Application.ScreenUpdating = False

'Set row height and column width
Columns("A:B").ColumnWidth = 2
Columns("C:T").ColumnWidth = 8.5
Columns("U:U").ColumnWidth = 2
Rows("1:36").RowHeight = 15
Rows("2:2").RowHeight = 20

' Unlock slicer positions
Call SlicerLockPosition(Me, False)

' Position the pivot chart slicers
Dim FormatSettings As SlicerFormatSettings

' Specify slicer settings
With FormatSettings
.Left = Application.InchesToPoints(10.4)
.Top = Application.InchesToPoints(1.1)
.Height = Application.InchesToPoints(1)
.Width = Application.InchesToPoints(2.5)
.NumberOfColumns = 2
.RowHeight = 18.72
.ColumnWidth = 82.08
End With

' STATUS SLICER
' Apply settings to slicer
With FormatSettings
.Top = Application.InchesToPoints(0.35) 'vertical
.Height = Application.InchesToPoints(0.4) 'height
End With
FormatSlicer "Catchup Slicer Status", FormatSettings
' Customize this slicer's settings
' No customization needed

' ART SLICER
' Apply settings to slicer
FormatSlicer "Catchup Slicer ART", FormatSettings
' Customize this slicer's settings
With FormatSettings
.Top = Application.InchesToPoints(1.1)
.Height = Application.InchesToPoints(1)
End With
' Apply settings to slicer
FormatSlicer "Catchup Slicer ART", FormatSettings

' TEAM SLICER
FormatSlicer "Catchup Slicer Team", FormatSettings
' Customize this slicer's settings
With FormatSettings
.Top = Application.InchesToPoints(2.2)
.Height = Application.InchesToPoints(1.5)
End With
' Apply settings to slicer
FormatSlicer "Catchup Slicer Team", FormatSettings

' PI SLICER
' Apply settings to slicer
FormatSlicer "Catchup Slicer PI", FormatSettings
' Customize this slicer's settings
With FormatSettings
.Top = Application.InchesToPoints(3.8)
.Height = Application.InchesToPoints(1)
End With
' Apply settings to slicer
FormatSlicer "Catchup Slicer PI", FormatSettings

' SPRINT SLICER
' Apply settings to slicer
FormatSlicer "Catchup Slicer Sprint", FormatSettings
' Customize this slicer's settings
With FormatSettings
.Top = Application.InchesToPoints(4.9)
.Height = Application.InchesToPoints(1.2)
End With
' Apply settings to slicer
FormatSlicer "Catchup Slicer Sprint", FormatSettings

'Lock slicer positions
Call SlicerLockPosition(Me, True)

'Move cursor to A1
Call MoveCursorToTop

Application.ScreenUpdating = True

End Sub

Hi again -

I have what I hope is a quick follow-up on this. I added two properties to the "SlicerFormatSettings" type: CrossFilterType and SortItems. In reading through the Microsoft documentation, I believe they can be set to values "xlSlicerCrossFilterHideButtonsWithNoData" and "xlSlicerCrossFilterHideButtonsWithNoData", respectively. However, when I try to apply the values to the properties it doesn't work (and it doesn't throw an error). Not sure what I'm doing wrong.  

Option Explicit

Type SlicerFormatSettings
  Left As Single
  Top As Single
  Height As Single
  Width As Single
  NumberOfColumns As Long
  RowHeight As Double
  ColumnWidth As Double
  CrossFilterType As Single
  SortItems As Single
End Type

Private Sub Worksheet_Activate()

' Specify slicer settings
With FormatSettings
  .Left = Application.InchesToPoints(10.4)
  .Top = Application.InchesToPoints(1.1)
  .Height = Application.InchesToPoints(1)
  .Width = Application.InchesToPoints(2.5)
  .NumberOfColumns = 2
  .RowHeight = 18.72
  .ColumnWidth = 82.08
  .CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData ' This is new, but not working
  .SortItems = xlSlicerCrossFilterHideButtonsWithNoData ' This is new, but not working
End With

' STATUS SLICER
' Apply settings above to slicer
FormatSlicer "CI Slicer Status", FormatSettings

End Sub

Hi Shawn,

.CrossFilterType applies to a SlicerCache object, NOT to a Slicer object.

https://docs.microsoft.com/en-us/office/vba/api/excel.slicercache.crossfiltertype

Did you amend the FormatSlicer code to actually use those new settings?

YES... The error is "Object doesn't support this property or method." That error occurs on both the ".CrossFilterType = SlicerFormat.CrossFilterType" and ".CrossFilterType = SlicerFormat.CrossFilterType" lines of the FormatSlicer routine.

So here is the whole thing...

Option Explicit

Type SlicerFormatSettings
  Left As Single
  Top As Single
  Height As Single
  Width As Single
  NumberOfColumns As Long
  RowHeight As Double
  ColumnWidth As Double
  CrossFilterType As Single 'This is what I'm trying to add
  SortItems As Single 'This is what I'm trying to add
End Type

Private Sub Worksheet_Activate()

' Specify slicer settings
With FormatSettings
  .Left = Application.InchesToPoints(10.4)
  .Top = Application.InchesToPoints(1.1)
  .Height = Application.InchesToPoints(1)
  .Width = Application.InchesToPoints(2.5)
  .NumberOfColumns = 2
  .RowHeight = 18.72
  .ColumnWidth = 82.08
  .CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData ' This is new, but not working
  .SortItems = xlSlicerCrossFilterHideButtonsWithNoData ' This is new, but not working
End With

' STATUS SLICER
' Apply settings above to slicer
FormatSlicer "CI Slicer Status", FormatSettings

End Sub

Sub FormatSlicer(SlicerName As String, SlicerFormat As SlicerFormatSettings)

Dim theSlicer As Slicer
Set theSlicer = GetSlicer(SlicerName)

If Not theSlicer Is Nothing Then
With theSlicer
.NumberOfColumns = SlicerFormat.NumberOfColumns
.RowHeight = SlicerFormat.RowHeight
.ColumnWidth = SlicerFormat.ColumnWidth
With .Shape
.Left = SlicerFormat.Left
.Top = SlicerFormat.Top
.Height = SlicerFormat.Height
.Width = SlicerFormat.Width
.CrossFilterType = SlicerFormat.CrossFilterType 'Causes error 438
.SortItems = SlicerFormat.SortItems 'Causes error 438
End With
Debug.Print "Attributes applied"
End With
End If

End Sub

As Catalin said earlier, those two properties belong to the slicer, so they need to be in the With theSlicer block, not related to its Shape

Hi Velouria,

just a minor correction:

As Catalin said earlier, those two properties belong to the slicer, so they need to be in the With theSlicer block, not related to its Shape

As mentioned previously, .CrossFilterType is not a property of shapes or Slicers, it's a SlicerCache object property.

Thanks Catalin - I didn’t read closely enough!

So to clarify, you would need:

Sub FormatSlicer(SlicerName As String, SlicerFormat As SlicerFormatSettings)

Dim theSlicer As Slicer
Set theSlicer = GetSlicer(SlicerName)

If Not theSlicer Is Nothing Then
With theSlicer
.NumberOfColumns = SlicerFormat.NumberOfColumns
.RowHeight = SlicerFormat.RowHeight
.ColumnWidth = SlicerFormat.ColumnWidth

With .SlicerCache

   .CrossFilterType = SlicerFormat.CrossFilterType
   .SortItems = SlicerFormat.SortItems

End With
With .Shape
.Left = SlicerFormat.Left
.Top = SlicerFormat.Top
.Height = SlicerFormat.Height
.Width = SlicerFormat.Width

End With
Debug.Print "Attributes applied"
End With
End If

End Sub

Hmmm. Gotta say, that's certainly more elegant than what I had written. I'm still not understanding, because my code throws an error. I've read some other forum pots posts and even watched some videos, but it's not clicking for me. I loaded the code with debug.print so I can follow execution and I see where it's breaking, but I'm not sure why.

MODULE

Option Explicit

Type SlicerFormatSettings
Left As Single
Top As Single
Height As Single
Width As Single
NumberOfColumns As Long
ColumnWidth As Double
RowHeight As Double
CrossFilterType As Single
SortItems As Single
Style As Style
DisplayHeader As Boolean
End Type

Sub TurnStuffOff()
Debug.Print Chr(10)
Debug.Print "Application.EnableEvents = TurnStuffOff: STARTING"
Debug.Print "Application.Calculation = xlManual"
Application.Calculation = xlManual
Debug.Print "Application.ScreenUpdating = False"
Application.ScreenUpdating = False
Debug.Print "Application.EnableEvents = False"
Application.EnableEvents = False
Debug.Print "Application.EnableEvents = TurnStuffOff: DONE"

End Sub

Sub TurnStuffOn()
Debug.Print Chr(10)
Debug.Print "Application.EnableEvents = TurnStuffOn: STARTING"
Debug.Print "Application.Calculation = xlAutomatic"
Application.Calculation = xlAutomatic
Debug.Print "Application.ScreenUpdating = True"
Application.ScreenUpdating = True
Debug.Print "Application.EnableEvents = True"
Application.EnableEvents = True
Debug.Print "Application.EnableEvents = TurnStuffOn: DONE"

End Sub

Sub SlicerLockPosition(ws As Worksheet, LockFlag As Boolean)
Debug.Print Chr(10)
Debug.Print "SlicerLockPosition STARTING"

Dim myShape As Shape
Dim mySlicer As Slicer
Dim N As Long

' In case of run-time error, move to next statement and continue execution
On Error Resume Next
N = 0

For Each myShape In ws.Shapes ' Loop through all slicers on sheet
If myShape.Type = msoSlicer Then
Set mySlicer = GetSlicer(myShape.Name)
If Not mySlicer Is Nothing Then
N = N + 1
Debug.Print "Slicer Shape DisableMoveResizeUI: " & N
mySlicer.DisableMoveResizeUI = LockFlag
End If
End If
Next myShape

Debug.Print "SlicerLockPosition DONE"
End Sub

Function GetSlicer(sName As String) As Slicer
Debug.Print Chr(10)
Debug.Print "GetSlicer STARTING"

Dim N As Long
Dim C As Long

' In case of run-time error, move to next statement and continue execution
On Error Resume Next

C = ActiveWorkbook.SlicerCaches.Count
For N = 1 To C
Set GetSlicer = ActiveWorkbook.SlicerCaches(N).Slicers(sName)
If Not GetSlicer Is Nothing Then Exit Function
Debug.Print "GetSlicer " & N & " of " & C & ": " & sName
Next N

Debug.Print "GetSlicer DONE"
End Function

Sub FormatSlicer(SlicerName As String, SlicerFormat As SlicerFormatSettings)
Debug.Print Chr(10)
Debug.Print "FormatSlicer STARTING: " & SlicerName

Dim theSlicer As Slicer
Set theSlicer = GetSlicer(SlicerName)

If Not theSlicer Is Nothing Then
With theSlicer
Debug.Print "FormatSlicer: Number of Columns"
.NumberOfColumns = SlicerFormat.NumberOfColumns
Debug.Print "FormatSlicer: Column Width"
.ColumnWidth = SlicerFormat.ColumnWidth
Debug.Print "FormatSlicer: Row Height"
.RowHeight = SlicerFormat.RowHeight

With .SlicerCache
Debug.Print "FormatSlicer Cache: CrossFilterType"
'This next line triggers ERR 1004: Application-defined or object-defined error
' .CrossFilterType = SlicerFormat.CrossFilterType
Debug.Print "FormatSlicer Cache: SortItems"
.SortItems = SlicerFormat.SortItems
End With

With .Shape
Debug.Print "FormatSlicer Shape: Left"
.Left = SlicerFormat.Left
Debug.Print "FormatSlicer Shape: Top"
.Top = SlicerFormat.Top
Debug.Print "FormatSlicer Shape: Height"
.Height = SlicerFormat.Height
Debug.Print "FormatSlicer Shape: Width"
.Width = SlicerFormat.Width
End With

End With

Else
Debug.Print "FormatSlicer: NOTHING"
End If

Debug.Print "FormatSlicer DONE: " & SlicerName
End Sub

Sub PositionCursor(ByVal strDestinationCol As String, ByVal dblDestinationRow As Double)

Dim strReference As String
strReference = CStr(strDestinationCol & dblDestinationRow)

Application.Goto reference:=Range(strReference), Scroll:=True
Range(strReference).Select

End Sub

Private Sub Worksheet_Activate()
Debug.Print "Worksheet_Activate: STARTING"

' In case of run-time error, display error message and stop execution
On Error GoTo ERROR_HANDLER

' Return application settings to normal
Debug.Print "Speed up application settings"
Call TurnStuffOff

' Set row height and column width
Debug.Print "Set row height and column width"
Columns("A:B").ColumnWidth = 2
Columns("C:T").ColumnWidth = 8.5
Columns("U:U").ColumnWidth = 2
Rows("1:36").RowHeight = 15
Rows("2:2").RowHeight = 20

' Position common objects
Debug.Print "Position common objects"
'Call PositionCommonObjects ' disabled for forum post

' Unlock slicer positions
Debug.Print "Unlock slicer positions"
'Call SlicerLockPosition(Me, False) 'disabled for forum post

Debug.Print "Configure common FormatSetting"
Dim FormatSettings As SlicerFormatSettings

' ******
' Slicer setting will be applied to FOUR slicers on this sheet
' Some property values apply to ALL four slicers
' Some property values apply to ONE slicer
' ***

' Specify COMMON slicer settings to apply to ALL slicers
' Specify slicer settings
With FormatSettings
.Left = Application.InchesToPoints(10.4)
.Width = Application.InchesToPoints(2.5)
.RowHeight = 18.72
.NumberOfColumns = 2
.ColumnWidth = 82.08
.CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData
.SortItems = xlSlicerCrossFilterHideButtonsWithNoData
' .Style = "SlicerStyleDark5" ' THIS TRIGGERS INVALID USE OF PROPERTY
.DisplayHeader = True
End With

Debug.Print "Apply common FormatSetting to all slicers"
' Apply common property values to each slicer on sheet
Debug.Print "Slicer: ART"
FormatSlicer "CI Slicer ART", FormatSettings
Debug.Print "Slicer: Team"
FormatSlicer "CI Slicer Team", FormatSettings
Debug.Print "Slicer: PI"
FormatSlicer "CI Slicer PI", FormatSettings
Debug.Print "Slicer: Sprint"
FormatSlicer "CI Slicer Sprint", FormatSettings

' The sections below apply the unique property
' values to each slicer on sheet

' ***
' Format the ART slicer with unique property values
Debug.Print "Format Slicer: ART"

' Set slicer position
' Not really needed since these are the same as common settings
With FormatSettings
.Top = Application.InchesToPoints(1.1)
.Height = Application.InchesToPoints(1)
End With
' Apply unique property values to ART slicer
FormatSlicer "CI Slicer ART", FormatSettings

' ***
' Format the TEAM slicer with unique property values
Debug.Print "Format Slicer: Team"

' Set slicer position
With FormatSettings
.Top = Application.InchesToPoints(2.15)
.Height = Application.InchesToPoints(1.85)
End With
' Apply unique property values to TEAM slicer
FormatSlicer "CI Slicer Team", FormatSettings

' ***
' Format the PI slicer with unique property values
Debug.Print "Format Slicer: PI"

' Set slicer position
With FormatSettings
.Top = Application.InchesToPoints(4.05)
.Height = Application.InchesToPoints(1)
.NumberOfColumns = 4
.ColumnWidth = 20
End With
' Apply unique property values to PI slicer
FormatSlicer "CI Slicer PI", FormatSettings

' ***
' Format the SPRINT slicer with unique property values
Debug.Print "Format Slicer: Sprint"

' Set slicer position
With FormatSettings
.Top = Application.InchesToPoints(5.1)
.Height = Application.InchesToPoints(1)
.NumberOfColumns = 4
.ColumnWidth = 20
End With
' Apply unique property values to Sprint slicer
FormatSlicer "CI Slicer Sprint", FormatSettings

' Lock slicer positions
Debug.Print "Lock Slicer positions"
'Call SlicerLockPosition(Me, True) 'disabled for forum post

' Move cursor to A1
Debug.Print "Move cursor to A:1"
Call PositionCursor("A", 1)

' Skip over error handler
GoTo GOODBYE

ERROR_HANDLER:

' Display error
Debug.Print Chr(10)
Debug.Print "ERROR ************************"
Debug.Print "ERROR Worksheet_Activate(ReportConfidence): " & Err.Number & ": " & Err.Description
MsgBox Err.Number & ": " & Err.Description, vbCritical + vbOKOnly, "Worksheet_Activate(ReportConfidence)"
Debug.Print "ERROR ***************************"
Debug.Print Chr(10)

GOODBYE:

' Return application settings to normal
Debug.Print "Return application settings to normal"
Call TurnStuffOn

Debug.Print "Worksheet_Activate: DONE"

End Sub

CrossFilterType and SortItems should both be Long not Single.

.SortItems = xlSlicerCrossFilterHideButtonsWithNoData

is not a valid value for SortItems - it should be one of:

xlSlicerSortAscending

xlSlicerSortDataSourceOrder

xlSlicerSortDescending

If you are using an OLAP data source, you have to use the CrossFilterType property of the relevant SlicerCacheLevel, not the SlicerCache.

Thank you. Your help got everything working. Much appreciated.