Does anyone have attractive slicer formats they'd like to share? I'm tired of the basics but creating an eye-catching alternative takes a lot of time, and of course they don't easily transfer to another worksheet.
To start the ball rolling I've got VBA for one. Someone gave the design on a Chandoo challenge quite a while ago and I thought it was nice enough to duplicate. I welcome any alternatives the forum can provide.
To start the ball rolling I've got VBA for one. Someone gave the design on a Chandoo challenge quite a while ago and I thought it was nice enough to duplicate. I welcome any alternatives the forum can provide.
Code:
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Name: Slicer_CreateCleanUnderlineStyle
' VB Version: 7.01
' Author: GMF
' Copyright: Creative Commons License BY-NC
' Created: Wednesday, March 15, 2017
' Comments: Wednesday, March 15, 2017
' Purpose: Create a slicer style with fixed colors
'
' Inputs: Param Name Type Meaning
' ----- ---- ---- -------
'
' Global Used:
' Module used:
'
' Called by: Method Component
' ------ ---------
' Slicer_AddCustomToWorksheet modPowerBI
'
' Calls: Method Component
' ------ ---------
' Slicer_Clear_Formats modPowerBI
' Item clsColorSchemes
' Get_Theme_Colors modColors
'
' API calls: Function DLL/Lib Declared
' -------- ------- --------
'
' Modified:
'------------------------------------------------------------
' Notes:
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Public Sub Slicer_CreateCleanUnderlineStyle()
Const strMARKERNAME = "Slicer Style Clean Fixed Color"
Const HEADERFILL = xlThemeColorAccent5
Const SLICERFONT = "Segoe UI"
Const PRIMARYCOLORTHEME = 4
Const CONTRASTCOLORTHEME = 9
Const CONTRASTCOLORTHEME2 = 6
Dim lHeaderFill As Integer
Dim iClrTheme As Integer
Dim dblClrTint As Double
' Always copy an existing slicer style
ActiveWorkbook.TableStyles("SlicerStyleLight1").Duplicate strMARKERNAME
With ActiveWorkbook.TableStyles(strMARKERNAME)
.ShowAsAvailablePivotTableStyle = False
.ShowAsAvailableTableStyle = False
.ShowAsAvailableSlicerStyle = True
.ShowAsAvailableTimelineStyle = False
End With
Slicer_Clear_Formats strMARKERNAME
'----------------------------------------
' Set the areas
'----------------------------------------
'-----Whole slicer elements-----
Get_Theme_Colors 1, 1, iClrTheme%, dblClrTint#
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlWholeTable).Font
.name = SLICERFONT
.ThemeFont = xlThemeFontNone
.TintAndShade = dblClrTint#
.ThemeColor = iClrTheme%
End With
' Header Section
' Font
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlHeaderRow).Font
.FontStyle = "Bold"
.Size = 9
.Color = RGB(0, 0, 0)
End With
' No Fill
' Border
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlHeaderRow).Borders(xlEdgeBottom)
.Color = RGB(245, 0, 0)
.Weight = xlMedium
.LineStyle = xlContinuous
End With
'-----Unselected Item with data-----
' Font
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlSlicerUnselectedItemWithData).Font
.Size = 9
.FontStyle = "Normal"
End With
' No Border
' No Fill
' Unselected item with no data
' Font
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlSlicerUnselectedItemWithNoData).Font
.Strikethrough = True
End With
' No Fill
' No Border
' Selected item with data
' Font
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlSlicerSelectedItemWithData).Font
.name = SLICERFONT
.FontStyle = "Bold"
.Size = 9
.Color = RGB(255, 255, 255)
End With
' No Border
' Fill
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlSlicerSelectedItemWithData).Interior
.Color = RGB(0, 176, 240)
End With
' Selected item with no data
' No Fill
' No Border
' Font
' Font
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlSlicerSelectedItemWithNoData).Font
.name = SLICERFONT
.Color = RGB(166, 166, 166)
End With
' Hover unselected with data
' Font color
' No Fill
' Border
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlSlicerHoveredUnselectedItemWithData).Borders(xlEdgeBottom)
.Color = RGB(0, 151, 204)
.Weight = xlThick
.LineStyle = xlContinuous
End With
' Hover selected item with data
' Font color
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlSlicerHoveredSelectedItemWithData).Font
.Bold = True
.Size = 9
.Color = RGB(255, 255, 255)
End With
' Interior fill
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlSlicerHoveredSelectedItemWithData).Interior
.Color = RGB(0, 112, 192)
End With
' No Border
' Hover unselected no data
' No Font change
' No border
' Interior gradient
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlSlicerHoveredUnselectedItemWithNoData).Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 45
.Gradient.ColorStops.Clear
End With
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlSlicerHoveredUnselectedItemWithNoData).Interior.Gradient.ColorStops.Add(0)
.Color = RGB(248, 225, 98)
End With
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlSlicerHoveredUnselectedItemWithNoData).Interior.Gradient.ColorStops.Add(0.5)
.Color = RGB(252, 247, 224)
End With
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlSlicerHoveredUnselectedItemWithNoData).Interior.Gradient.ColorStops.Add(1)
.Color = RGB(248, 225, 98)
End With
' Hover selected item no data
' No Font change
' No border
' Interior gradient
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlSlicerHoveredSelectedItemWithNoData).Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 90
.Gradient.ColorStops.Clear
End With
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlSlicerHoveredSelectedItemWithNoData).Interior.Gradient.ColorStops.Add(0)
.Color = RGB(248, 225, 98)
End With
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlSlicerHoveredSelectedItemWithNoData).Interior.Gradient.ColorStops.Add(0.5)
.Color = RGB(252, 247, 224)
End With
With ActiveWorkbook.TableStyles(strMARKERNAME).TableStyleElements(xlSlicerHoveredSelectedItemWithNoData).Interior.Gradient.ColorStops.Add(1)
.Color = RGB(248, 225, 98)
End With
End Sub