MiniPost 2 of 2: Changing Slicer Fonts With Macros

 
In Tuesday’s post, I showed how WingDings and other symbolic fonts can be used on slicers for an interesting effect.

But there was a lot of manual work involved with changing slicer style settings in order to accomplish that – eight repetitive steps.

I’m back today to share a macro that makes that process much more painless.

I’d Like to Thank the Academy…  of Macro Recording

I’ve said it before and I’ll say it forever – the ability to record macros in Excel is the single greatest reference on how to write macros of your own.  And I leaned heavily on that recording feature to write what I’m sharing here.

So even if you’re not a macro person, I recommend trying out macro recording.  There’s some “how to” info at the end of a post from March.


The ActiveSlicer Object!

The one “magic ingredient” that macro recording did NOT provide was this bad boy – it tells you which slicer is currently selected!  I didn’t know about this until Tuesday, when I discovered it in Excel help.

This.  Changes.  Everything.

Until now I didn’t think I could detect which slicer was detected, and so many of my other macros are based on knowing the name of the slicer, which is tedious to get.  Macros like “turn off cross filtering” will be much better too now that I can just select the one I want in Excel and run the macro.

And Now…  The Macro

Apologies, I formatted this to fit my screen in the macro editor, not the web, and am too lazy (this time) to reformat it.  Paste it into the VBA macro environment though and it should look ok.  I did go ahead and format the comments as green though, and the part that you edit in order to control the font in yellow.

Note:  this changes all slicer fonts EXCEPT the header font!  This is pretty specifically useful in the WingDings case, and not what you’d use to modify the font of the entire slicer.

Sub ChangeSlicerFonts()
    Dim oSlicer As Slicer
    Dim sStyle As String
    Dim sFont As String
    Dim iFontSize As Integer
   
    ‘Set your desired font and font size here
    ‘WARNING – If you misspell your fontname, you will get no error but the font change won’t take!
    ‘I lost 30 frustrating minutes to that problem, so don’t be like me ;-)

    sFont = “Wingdings”
    iFontSize = 20
   
    ‘Finds the active slicer and what style is used by that slicer
    ‘*****NOTE this MUST be a custom style!!!!  The built-in styles are read-only and CANNOT be modified*****
    ‘*****To modify one of the built-in styles, right click it in the ribbon gallery and click Duplicate…
    ‘*****Then make sure you USE that new style, as the original slicer remains using the built-in style until you switch to it

    Set oSlicer = ActiveWorkbook.ActiveSlicer
    sStyle = oSlicer.Style
   
    ‘Calls the other two subs to set font and size
    SetAllSlicerItemFonts sStyle, sFont
    SetAllSlicerItemFontSizes sStyle, iFontSize
   
End Sub

Sub SetAllSlicerItemFonts(sStyle As String, sFont As String)
   
    ‘set all 8 different slicer item style elements
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerUnselectedItemWithNoData).Font.Name = sFont
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerSelectedItemWithNoData).Font.Name = sFont
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerUnselectedItemWithData).Font.Name = sFont
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerSelectedItemWithData).Font.Name = sFont
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerHoveredSelectedItemWithData).Font.Name = sFont
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerHoveredSelectedItemWithNoData).Font.Name = sFont
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerHoveredUnselectedItemWithData).Font.Name = sFont
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerHoveredUnselectedItemWithNoData).Font.Name = sFont
   
    ‘on same 8 elements, clear the ThemeFont or your desired font won’t show up (don’t ask me why, I don’t know)
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerUnselectedItemWithNoData).Font.ThemeFont = xlThemeFontNone
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerSelectedItemWithNoData).Font.ThemeFont = xlThemeFontNone
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerUnselectedItemWithData).Font.ThemeFont = xlThemeFontNone
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerSelectedItemWithData).Font.ThemeFont = xlThemeFontNone
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerHoveredSelectedItemWithData).Font.ThemeFont = xlThemeFontNone
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerHoveredSelectedItemWithNoData).Font.ThemeFont = xlThemeFontNone
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerHoveredUnselectedItemWithData).Font.ThemeFont = xlThemeFontNone
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerHoveredUnselectedItemWithNoData).Font.ThemeFont = xlThemeFontNone
   
   
End Sub

Sub SetAllSlicerItemFontSizes(sStyle As String, iFontSize As Integer)
   
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerUnselectedItemWithNoData).Font.Size = iFontSize
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerSelectedItemWithNoData).Font.Size = iFontSize
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerUnselectedItemWithData).Font.Size = iFontSize
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerSelectedItemWithData).Font.Size = iFontSize
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerHoveredSelectedItemWithData).Font.Size = iFontSize
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerHoveredSelectedItemWithNoData).Font.Size = iFontSize
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerHoveredUnselectedItemWithData).Font.Size = iFontSize
    ActiveWorkbook.TableStyles(sStyle).TableStyleElements(xlSlicerHoveredUnselectedItemWithNoData).Font.Size = iFontSize
   
End Sub

If You Are Changing The Header Font Too…

image

If You’re Changing ALL of the Fonts on a Slicer, This Whole Slicer Element Is Your Friend
(There Are Not Eight Steps Required)

Remember that in this case we were trying to change just the font of the slicer items, the tiles, and leave the header alone.  We didn’t want WingDings as the caption font, we still want it to read as the “Region” slicer for instance.

And that’s why we had eight manual steps – if you wanted to change the header font too, you can just change the “Whole Slicer” and avoid the whole eight-step process.

4 Responses to MiniPost 2 of 2: Changing Slicer Fonts With Macros

  1. Jason McKenzie says:

    This code failed for me on: sStyle = oSlicer.Style

    ‘Calls the other two subs to set font and size
    SetAllSlicerItemFonts sStyle, sFont
    SetAllSlicerItemFontSizes sStyle, iFontSize

    • powerpivotpro says:

      Do you know how to step through macros and set watches to see what oSlicer.Style is set to before that line runs?

  2. Jason McKenzie says:

    I can step through, but I’m unfamiliar with watches.

Leave a Reply