Log In
Register
Home
December 7, 2021
Offline
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
November 8, 2013
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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
December 7, 2021
Offline
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
December 7, 2021
Offline
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
November 8, 2013
Offline
Hi Shawn,
.CrossFilterType applies to a SlicerCache object, NOT to a Slicer object.
https://docs.microsoft.com/en-.....filtertype
Trusted Members
Moderators
November 1, 2018
Offline
Did you amend the FormatSlicer code to actually use those new settings?
December 7, 2021
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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
November 8, 2013
Offline
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.
Trusted Members
Moderators
November 1, 2018
Offline
Thanks Catalin - I didn’t read closely enough!
Trusted Members
Moderators
November 1, 2018
Offline
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
December 7, 2021
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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.
1 Guest(s)
