Excel Forum

VBA to position and format slicers

Shawn Wallack

Member

Members

Forum Posts: 77

Member Since:
December 7, 2021

Offline

April 25, 2022 - 5:56 am

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:

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

See Answer

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

April 25, 2022 - 2:38 pm

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

Attachments

Screenshot-2022-04-25-073007.png (336 KB)

Velouria

London or thereabouts

Moderator

Members

Trusted Members

Moderators

Forum Posts: 832

Member Since:
November 1, 2018

Offline

April 25, 2022 - 6:38 pm

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

Answers Post

Shawn Wallack

Member

Members

Forum Posts: 77

Member Since:
December 7, 2021

Offline

April 25, 2022 - 11:27 pm

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

' 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

Shawn Wallack

Member

Members

Forum Posts: 77

Member Since:
December 7, 2021

Offline

April 29, 2022 - 10:19 pm

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

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

April 29, 2022 - 11:03 pm

Hi Shawn,

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

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

Attachments

Screenshot-2022-04-29-160206.png (178 KB)

Velouria

London or thereabouts

Moderator

Members

Trusted Members

Moderators

Forum Posts: 832

Member Since:
November 1, 2018

Offline

April 30, 2022 - 12:20 am

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

Shawn Wallack

Member

Members

Forum Posts: 77

Member Since:
December 7, 2021

Offline

May 1, 2022 - 5:55 am

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

Private Sub Worksheet_Activate()

' 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

Velouria

London or thereabouts

Moderator

Members

Trusted Members

Moderators

Forum Posts: 832

Member Since:
November 1, 2018

Offline

May 1, 2022 - 8:49 pm

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

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

May 1, 2022 - 10:15 pm

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.

Velouria

London or thereabouts

Moderator

Members

Trusted Members

Moderators

Forum Posts: 832

Member Since:
November 1, 2018

Offline

May 1, 2022 - 10:42 pm

Thanks Catalin - I didn’t read closely enough!

Velouria

London or thereabouts

Moderator

Members

Trusted Members

Moderators

Forum Posts: 832

Member Since:
November 1, 2018

Offline

May 3, 2022 - 9:30 pm

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

Shawn Wallack

Member

Members

Forum Posts: 77

Member Since:
December 7, 2021

Offline

May 5, 2022 - 3:04 am

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

Velouria

London or thereabouts

Moderator

Members

Trusted Members

Moderators

Forum Posts: 832

Member Since:
November 1, 2018

Offline

May 5, 2022 - 8:03 pm

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.

Shawn Wallack

Member

Members

Forum Posts: 77

Member Since:
December 7, 2021

Offline

May 13, 2022 - 4:50 am

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

Forum Timezone: Australia/Brisbane

Most Users Ever Online: 245

Currently Online: Charles Johnson

Guest(s) 8

Currently Browsing this Page:
1 Guest(s)

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