Attractive slicer formats?

macfuller

Active Member
Joined
Apr 30, 2014
Messages
319
Office Version
  1. 365
Platform
  1. Windows
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.

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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top