• 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
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

VBA to position and format slicers|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / VBA to position and format slicers|VBA & Macros|Excel Forum|My Online Training Hub
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 & MacrosVBA to position and format slicers
sp_PrintTopic sp_TopicIcon
VBA to position and format slicers
Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
1
April 25, 2022 - 5:56 am
sp_Permalink sp_Print sp_EditHistory

Hello -

I have a workbook with several worksheets each of which use slicers. Each slicer has a unique name, of course (as I've tried to indicate below in the code comments). I run the code below to position and format the slicers when each sheet is activated.

Here is what I'm trying to do:

  1. The code is replicated in multiple places (every sheet). I'd like to move the code to a common module and just pass it the slicer ID as a parameter (and maybe the settings, too). That seems more efficient.
  2. I'd like to set ALL values for slicer position, layout, size, and properties The code works well to set the position of the slicers, but I can't figure out how to set the button column count, height, and width. Those do not seem to be options when I use the "mySlicer" reference. 

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

On Error Resume Next
Application.ScreenUpdating = False

'Unlock slicer positions
Call SlicerLockPosition(False)

'Position the pivot chart slicers
Dim myShape As Shape

'Format Slicer - ART

'In this example, the slicer name is "Catchup Slicer ART"

'On other sheets it might be called "Velocity Slicer ART" or "Burndown Slicer ART"

Set myShape = ActiveSheet.Shapes("Catchup Slicer ART") 'ART slicer
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

'These are the control that trigger an error

'How can I format the buttons?

myShape.NumberOfColumns = 2
myShape.RowHeight = 18.72
myShape.ColumnWidth = 82.08

'Format Slicer - Team
Set myShape = ActiveSheet.Shapes("Catchup Slicer TEAM") 'Team slicer
myShape.Left = Application.InchesToPoints(10.4) 'horizontal
myShape.Top = Application.InchesToPoints(2.15) 'vertical
myShape.Height = Application.InchesToPoints(1.85) 'height
myShape.Width = Application.InchesToPoints(2.5) 'width

'Format Slicer - Increment
Set myShape = ActiveSheet.Shapes("Catchup Slicer PI") 'PI slicer
myShape.Left = Application.InchesToPoints(10.4) 'horizontal
myShape.Top = Application.InchesToPoints(4.05) 'vertical
myShape.Height = Application.InchesToPoints(1) 'height
myShape.Width = Application.InchesToPoints(2.5) 'width

'Format Slicer - Iteration
Set myShape = ActiveSheet.Shapes("Catchup Slicer Sprint") 'Sprint slicer
myShape.Left = Application.InchesToPoints(10.4) 'horizontal
myShape.Top = Application.InchesToPoints(5.1) 'vertical
myShape.Height = Application.InchesToPoints(1) 'height
myShape.Width = Application.InchesToPoints(2.5) 'width

'Format Slicer - Status
Set myShape = ActiveSheet.Shapes("Catchup Slicer Status") 'Status slicer
myShape.Left = Application.InchesToPoints(10.4) 'horizontal
myShape.Top = Application.InchesToPoints(0.35) 'vertical
myShape.Height = Application.InchesToPoints(0.4)'height
myShape.Width = Application.InchesToPoints(2.5)'width

'Lock slicer positions
Call SlicerLockPosition(True)

Application.ScreenUpdating = True

End Sub

Sub SlicerLockPosition(LockFlag As Boolean)

  Dim myShape As Shape

  Dim mySlicer As Slicer

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

End Sub

sp_AnswersTopicSeeAnswer See Answer
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
April 25, 2022 - 2:38 pm
sp_Permalink sp_Print

Hi Shawn,

The slicer does have a NumberOfColumns property:

Screenshot-2022-04-25-073007.pngImage Enlarger

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

sp_PlupAttachments Attachments
  • sp_PlupImage Screenshot-2022-04-25-073007.png (336 KB)
Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 625
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
3
April 25, 2022 - 6:38 pm
sp_Permalink sp_Print

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).

sp_AnswersTopicAnswer
Answers Post
Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
4
April 25, 2022 - 11:27 pm
sp_Permalink sp_Print

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

Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
5
April 29, 2022 - 10:19 pm
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
April 29, 2022 - 11:03 pm
sp_Permalink sp_Print

Hi Shawn,

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

https://docs.microsoft.com/en-.....filtertype

Screenshot-2022-04-29-160206.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage Screenshot-2022-04-29-160206.png (178 KB)
Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 625
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
7
April 30, 2022 - 12:20 am
sp_Permalink sp_Print

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

Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
8
May 1, 2022 - 5:55 am
sp_Permalink sp_Print

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

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 625
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
9
May 1, 2022 - 8:49 pm
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
10
May 1, 2022 - 10:15 pm
sp_Permalink sp_Print

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.

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 625
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
11
May 1, 2022 - 10:42 pm
sp_Permalink sp_Print

Thanks Catalin - I didn’t read closely enough!

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 625
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
12
May 3, 2022 - 9:30 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
13
May 5, 2022 - 3:04 am
sp_Permalink sp_Print

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

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 625
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
14
May 5, 2022 - 8:03 pm
sp_Permalink sp_Print

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.

Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
15
May 13, 2022 - 4:50 am
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Kim Knox, Bhuwan Devkota
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Raj Mattoo
Mark Luke
terimeri dooriyan
Forum Stats:
Groups: 3
Forums: 24
Topics: 6221
Posts: 27285

 

Member Stats:
Guest Posters: 49
Members: 31909
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.