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