Saving and Reapplying Pivot Chart Formatting

Saving and Reapplying Pivot Chart Formatting

I’m still wrestling with pivot charts, and boy are my metaphorical forearms big! Seriously though, I just recently became aware of the crazy problem of pivot charts losing their formatting. I’ve got a bunch of pivot charts with two x axes and other embellishments, and pretty much any change to the chart or the source pivot can erase all the carefully applied formatting. In this post I’ll outline a couple of ways to decrease, but by no means eliminate, the pain of losing your pivot chart formatting.

The Problem

Here’s an example – a chart with two axes, different chart types and non-standard colors. I’m quite pleased with its looks.

pivot chart

However, if I so much as resize a column in the source pivot… much-less-nice formatting.

pivot chart after pivot column resize

It gets worse. Look at what happens when I add and remove a field:

pivot chart formatting loss

One axis is eliminated without so much as a “by your leave,” the line graphs revert to columns and the colors regress to garish defaults. It’s a mess. Unchecking the field doesn’t undo the changes.

A Partial Solution

When I first encountered this issue my hopes were raised by the presence of a long Jon Acampora post on Jon Peltier’s blog. However the two solutions listed there have a huge drawback: they eliminate the use of pivot charts. Talk about throwing the baby out with the bath water! In the post’s comments a couple of people think they’ve found ways to make the formatting stick, but these didn’t work for me.

Looking around the web some more, I found two commands that help me as the chart developer. The first is the “Save as Template” command:

save as template command

The dialog saves to Excel’s Templates>Charts folder by default. My practice is to save early and often to the same distinctively named file:

save as template dialog

Then should my changes get wiped out, I avail myself of the “Change Chart Type” command.

change chart type command

Hey presto, there’s my template with the most recent changes. Yay!

change chart type dialog

VBA Automation

I wrote some VBA to automate this stuff. One of the routines below saves every template in the active workbook to the templates folder. It names the template with the worksheet and chart name to avoid errors from having charts with the same names on different sheets. Another routine applies a template to the active chart, assuming it can find one that meets the same SheetName_ChartName convention. Of course even if you rename or move a chart you can figure out what its template was saved at and apply it using the Change Chart Type command.

Here’s the code:

Sub SaveActiveChartTemplate()
Dim chtActive As Excel.Chart

If Not ActiveChart Is Nothing Then
    Set chtActive = ActiveChart
    SaveChartTemplate chtActive
    MsgBox "No Chart Selected"
End If
End Sub

Sub SaveAllChartTemplates()
Dim ws As Excel.Worksheet
Dim chtObject As Excel.ChartObject

For Each ws In ActiveWorkbook.Worksheets
    For Each chtObject In ws.ChartObjects
        SaveChartTemplate chtObject.Chart
    Next chtObject
Next ws
End Sub

Sub SaveChartTemplate(cht As Excel.Chart)
    'if no path specified then default folder: C:\Users\yourumbuddy\AppData\Roaming\Microsoft\Templates\Charts
    cht.SaveChartTemplate Replace(cht.Parent.Parent.Name & "_" & cht.Parent.Name & ".crtx", " ", "_")
End Sub

Sub ApplySavedTemplateToActiveChart()
Dim chtActive As Excel.Chart

If Not ActiveChart Is Nothing Then
    Set chtActive = ActiveChart
    chtActive.ApplyChartTemplate Replace(chtActive.Parent.Parent.Name & "_" & chtActive.Parent.Name & ".crtx", " ", "_")
    MsgBox "No Chart Selected"
End If
End Sub

Does this work for End Users?
Only the very motivated and patient ones, I’d say. If needed though, I think you could attach code like the above to events and maybe create something that would help them retain formatting as they pivot the charts.

Using TEXTJOIN to Create a SQL IN Clause

Using TEXTJOIN to Create a SQL IN Clause

I’ve been playing around with the new-to-2016 TextJoin function. My first use was to concatenate a bunch of cells for a comma-delimited parameter. TEXTJOIN works way better than the near-useless CONCATENATE function of yesterversions (and I can now drop Rick Rothstein’s very nice ConCat function from my personal macro workbook). One great TEXTJOIN feature is the ability to ignore blank cells in an input range. Another is that you can have multi-character delimiters, including characters like the CHAR(10) linefeed.

This makes it ideal for a type of utility used by many of us data wranglers: one that takes a column of values and formats it for use in a SQL IN clause:

TextJoined SQL List

The formula above is

="'" & TEXTJOIN("'," & CHAR(10) & "'",TRUE,A:A) & "'"

It’s for text values, so it wraps everything in single-quotes. If you were using it for numbers you’d remove these. It also includes a comma in the delimiter. And, my favorite part, it includes a linefeed to format the words in a one-word-per-row column. The beginning and the end of the formula simply add the starting and ending single-quotes.

In the picture above I have word wrap turned on to show the formatting, but you can turn it off and it will still paste into separate rows:

List Word Wrap off

You could take it two steps further and add the “IN” and opening and closing parentheses. My main goal though is to avoid the repetitive comma and single-quote part though. I’ll do it by hand for up to about 10 items but after that I want a formula like this.

A FormatForSqlList UDF

Of course, it would be really nice to have this as a UDF in my toolkit. Doing so would let me spiff it up a bit:

Public Function FormatForSqlList(ListRange As Excel.Range, _
    Optional ListIsText As Boolean = True) As String

If Val(Application.Version) < 16 Then
    FormatForSqlList = "requires Excel 2016 or higher"
    Exit Function
End If

FormatForSqlList = "(" & vbCrLf & IIf(ListIsText, "'", "") & _
    WorksheetFunction.TextJoin(IIf(ListIsText, "'", "") & "," & _
    vbCrLf & IIf(ListIsText, "'", ""), True, ListRange) & _
    IIf(ListIsText, "'", "") & vbCrLf & ")"
End Function

I don’t write many UDFs, so the above could probably use some refinement. I guess it would be nice if it took values directly instead of just from a range, but maybe not. My normal pattern is that I’m taking a bunch of results from a query in SQL Assistant or in Excel, and those both lend themselves well to just pasting into a column of cells.

The Double-Quote Problem

Unfortunately, both formulas have an unwanted side-effect. When you copy and paste from a one-cell comma-separated list with linefeeds to a text editor or SQL IDE, double-quotes are added at the start and end of the string. One solution is to paste the string to Word and then into the text editor, but that seems more cumbersome than just deleting the double-quotes. I assume I could do something with pasting to the Windows clipboard via a DataObject, but then I’d need to have a separate subroutine or maybe a userform. Those also seem clunky, so I’ll just see how much it bugs me. If you come up with a solution, please let us know.

Another Interesting and Way Fancier TEXTJOIN Function

Chris Webb has a nice blog post here about finding all selected items in a slicer using TEXTJOIN.

SaveCopyAs Using GetSaveAsFilename

SaveCopyAs Using GetSaveAsFilename

I’ve been tinkering with a routine that uses VBA’s SaveCopyAs function to make a timestamped backup of the active workbook. It lets you choose the location for the backup and sets the name to the workbook’s name followed by a timestamp. I had been using the msoFileDialogSaveAs dialog. However, it shows all the possible file extensions and descriptions which you might save a workbook as.:

Save as xlsb filter

And even though the Application.Dialogs object has Delete, Clear and Add functions, those don’t seem to work with the SaveAs and Open dialogsThis doesn’t make sense with SaveCopyAs, which only lets you save to the same file type. Since I want to limit the file extension to the one for the file getting copied, I went with GetSaveAsFilename. It lets you manage the extensions and descriptions that the user sees, for example “Excel Binary Workbook (*.xlsb):

Save copy as xlsb

The flip side of GetSaveAsFilename letting you tinker with the file descriptions and extensions is that you have to specify them from scratch. I’d like this routine to be flexible enough to work with all kinds of Excel files and any others you can open in Excel, and I’d like the file descriptions shown by my dialog to match the ones from Save As dialog. So, since msoFileDialogSaveAs contains all the extensions and descriptions you’ll see in a Save As dialog, I wrote a function that searches the msoFileDialogSaveAs.Filters to get the description and extension(s) that go with a particular extension.

Here’s the function:

Function GetFdSaveAsFilter(FilterExtension As String) As String()
Dim fdSaveAsFilter(1 To 2) As String
Dim fdFileDialogSaveAs As FileDialog
Dim fdFilter As FileDialogFilter
Dim FilterExtensions As Variant
Dim i As Long

Set fdFileDialogSaveAs = Application.FileDialog(msoFileDialogSaveAs)
For Each fdFilter In fdFileDialogSaveAs.Filters
    FilterExtensions = Split(fdFilter.Extensions, ",")
    For i = LBound(FilterExtensions) To UBound(FilterExtensions)
        If WorksheetFunction.Trim(FilterExtensions(i)) = "*" & FilterExtension Then
            fdSaveAsFilter(1) = fdFilter.Description
            fdSaveAsFilter(2) = fdFilter.Extensions
            GetFdSaveAsFilter = fdSaveAsFilter
            GoTo Exit_Point
        End If
    Next i
Next fdFilter

Set fdFileDialogSaveAs = Nothing

End Function

This function is called from my main routine, shown below.

Sub SaveWorkbookCopy()
Dim WorkbookToCopy As Excel.Workbook
Dim WorkbookExtension As String
Dim fdSaveAsFilter() As String
Dim WorkbookName As String

If ActiveWorkbook Is Nothing Then
    MsgBox "No active workbook."
    Exit Sub
End If

If ActiveWorkbook.Path = "" Then
    MsgBox "This workbook has never been saved."
    Exit Sub
End If

Set WorkbookToCopy = ActiveWorkbook
WorkbookExtension = Mid$(WorkbookToCopy.Name, InStrRev(WorkbookToCopy.Name, "."), 99)
fdSaveAsFilter = GetFdSaveAsFilter(WorkbookExtension)
WorkbookName = Application.GetSaveAsFilename(InitialFileName:=Replace(WorkbookToCopy.FullName, WorkbookExtension, "") & "_" &
'msoFileDialogSaveAs separates extensions with a comma, but GetSaveAsFilename uses a semicolon
GetTimestamp, FileFilter:=fdSaveAsFilter(1) & ", " & Replace(fdSaveAsFilter(2), ",", ";"), Title:="Save Copy As")
If WorkbookName = "False" Then
    Exit Sub
End If
WorkbookToCopy.SaveCopyAs WorkbookName
End Sub

GetTimeStamp is a one-line functions that returns a timestamp down to 1/100 of a secon

Function GetTimestamp() As String
GetTimestamp = Format(Now(), "yyyymmddhhmmss") & Right(Format(Timer, "#0.00"), 2)
End Function

Pivot Table Pivot Chart Navigator

Pivot Table Pivot Chart Navigator

This post is about navigating between pivot tables and pivot charts. The sample workbook contains a Pivot Table and Pivot Chart Navigator userform that lists the workbook’s pivot tables and takes you to them or their associated charts. The workbook also adds buttons to the chart and pivot table right-click menus. These buttons take you to the associated pivot chart or table. I used Ribbon XML for this last part since later versions of Excel don’t allow modification of the chart context menus with VBA. The downloadable workbook can be easily converted to an addin.

pivot chart context menu

I used to eschew pivot charts as far too clunky. Recently though I was given a project that contained many pivot charts. It seemed that, unless I’d just gotten much less picky (not likely), pivot charts work much better than I remembered. This impression was confirmed in a Jon Peltier post, so I know it’s true.

Using XML to Add to Right-Click Menus

As mentioned above, I’ve added a “Go to Source Pivot” button at the bottom of the chart context menu. I’d never used Ribbon XML to make a right-click menu before. The XML part is straightforward.

To create the button I used the Custom UI Editor and added a ContextMenu section to the XML. I also used the Microsoft’s NameX addin to figure out the name that refers to the chart context menu (ContextMenuChartArea) The XML for the chart and pivot table context menus is below. All of this, including links to the Custom UI Editor and the NameX addin, is covered very nicely in this MSDN post.

Since I’m already forced to use XML to modify the chart context menu, I used it for the pivot table context menu too, even though it can still be modified with VBA:

    <contextMenu idMso="ContextMenuChartArea">
     <button id="cmdGoToSourcePivot" label="Go To Source Pivot"
        getVisible = "cmdGoToSourcePivot_GetVisible"/>
    <contextMenu idMso="ContextMenuPivotTable">
     <button id="cmdGoToPivotChart" label="Go To Pivot Chart"
        onAction="cmdGoToPivotChart_onAction" />

VBA to Go To Source Pivot
The code to go to the source pivot is similar to that in my Finding a Pivot Chart’s Pivot Table post. It looks at the charts PivotLayout property, which only exists if a chart is based on a pivot table. I use this same property in the RibbonInvalidate method to only show the “Go To Pivot Table” button when the chart is a pivot chart. That’s one thing I like about programming the ribbon: the code to show or hide tabs, buttons and other controls is generally simpler than it is when using VBA.

VBA to Go To Pivot Chart
The code to go to a pivot table’s chart loops through all chart sheets and charts on worksheets looking for one whose source range is the pivot table’s range:

Function GetPivotChart(pvt As Excel.PivotTable) As Excel.Chart
Dim wbWithPivots As Excel.Workbook
Dim ws As Excel.Worksheet
Dim chtObject As Excel.ChartObject
Dim cht As Excel.Chart

Set wbWithPivots = pvt.Parent.Parent
For Each cht In wbWithPivots.Charts
    If Not cht.PivotLayout Is Nothing Then
        If cht.PivotLayout.PivotTable.TableRange1.Address(external:=True) = pvt.TableRange1.Address(external:=True) Then
            Set GetPivotChart = cht
            Exit Function
        End If
    End If
Next cht
For Each ws In wbWithPivots.Worksheets
    For Each chtObject In ws.ChartObjects
        Set cht = chtObject.Chart
        If Not cht.PivotLayout Is Nothing Then
            If cht.PivotLayout.PivotTable.TableRange1.Address(external:=True) = pvt.TableRange1.Address(external:=True) Then
                Set GetPivotChart = cht
                Exit Function
            End If
        End If
    Next chtObject
Next ws
End Function

PivotNavigator Form
The other element of the sample workbook is a simple-yet-powerful form that navigates through a workbook’s pivot tables and pivot charts.

pivot navigator form

The form opens up with a list of all the pivot tables in the active workbook. Selecting an item in the form list takes you to the selected pivot. Use the Ctrl key with the left and right arrows to toggle between a pivot and its associated chart.

The form is modeless and responds to selection changes in the workbook, updating the list selection when you click into a different pivot or chart. This functionality uses VBA from my last post, which raises an event every time any chart in a workbook is selected.

The sample workbook has the modified right-click menus, the navigation form and a button in the Developer tab to start the form. There’s even instructions!

Chart SelectionChange Event

Chart SelectionChange Event

I’m working on a pivot chart and pivot table navigator. It lets you click around between pivots and their associated charts. It consists of a small form with a list of the active workbook’s pivot tables. You use this list to navigate to pivots and their charts. When you choose a different pivot or chart in Excel itself I want the selected item in the list to change. I can use the SelectionChange event and the Range.PivotTable property to determine which pivot table has been selected. But SelectionChange doesn’t fire when you click into a chart. After messing about for a bit I’ve come up with a chart SelectionChange event that fires on chart selection and also includes all the stuff in a normal SelectionChange event.

The crux of the solution can be found in this excellent Jon Peltier post describing Chart events. It turns out charts are one of the Excel objects that supports WithEvents:

Excel withevents objects

This lets me create a tiny chart class that I can instantiate for each chart in all open workbooks.

Here’s the clsChart class:


Public WithEvents cht As Excel.Chart
Public cFullSelectionChange As clsFullSelectionChange

Private Sub cht_Activate()
Set cFullSelectionChange.Chart_Activated = cht
End Sub

It’s got the WithEvents declaration that allows us to capture chart events. In this case we’re capturing the event that occurs when a charge is activated. When that happens it pokes the Chart_Activated property in the clsFullSelectionChange class. This is the “parent” class that creates an instance of clsChart for each chart in the workbook.

FullSelectionChange Class That Includes Charts

Here’s the code for the cFullSelectionChange class:


Private cChart As clsChart
Public WithEvents app As Excel.Application
Private collCharts As Collection
Public Event PivotSelected(pvt As Excel.PivotTable)
Public Event ChartSelected(cht As Excel.Chart)
Public Event OtherSelected()

Private Sub Class_Initialize()
Dim Wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim chtObject As Excel.ChartObject
Dim cht As Excel.Chart

Set app = Application
Set collCharts = New Collection
For Each Wb In Application.Workbooks
    For Each cht In Wb.Charts
        Set cChart = New clsChart
        Set cChart.cFullSelectionChange = Me
        Set cChart.cht = cht
        collCharts.Add cChart
    Next cht
    For Each ws In Wb.Worksheets
        For Each chtObject In ws.ChartObjects
            Set cChart = New clsChart
            Set cChart.cFullSelectionChange = Me
            Set cChart.cht = chtObject.Chart
            collCharts.Add cChart
        Next chtObject
    Next ws
Next Wb
End Sub

Public Property Set Chart_Activated(cht As Excel.Chart)
RaiseEvent ChartSelected(cht)
End Property

Private Sub app_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim ActivePivot As Excel.PivotTable

On Error Resume Next
Set ActivePivot = ActiveCell.PivotTable
On Error GoTo 0
If Not ActivePivot Is Nothing Then
    RaiseEvent PivotSelected(ActivePivot)
    RaiseEvent OtherSelected
End If
End Sub

The last sub in the class, app_SheetSelectionChange, is a regular old application-level SheetSelectionChange event. It checks whether the ActiveCell is in a pivot table. Note that I could have used Selection just as easily as Activecell. I included this event in this class so that all the selection logic would be in one place, rather than putting it in the UserForm itself as I’d normally do. You’ll see below that this keeps the UserForm code quite simple.

The second-to-last sub is the Chart_Activated Property that gets poked by the individual chart classes when a chart is activated. It receives the activated chart object from the chart class.

Both these last two subs raise events that are hooked in the UserForm. The events are defined at the top of the module. They simply make the pivot table or chart available in the userform, much like an Sh or Target parameter in a built-in event. If you aren’t familiar with raising custom events the info at the end of Chip Pearson’s event page is very helpful.

The class’s Initialize routine creates instances of the chart class – one for every standalone or embedded chart in all open workbooks. If this isn’t familiar, take a look at the same Chip Pearson page linked above, specifically the Declaring WithEvents Variables section.

The UserForm
Because most of the work gets done in the two classes, the UserForm code is simple and powerful. The form looks like this, with textboxes to show either the selected chart or pivot table, if either:

Userform in action

UserForm Code

Private WithEvents cFullSelectionChange As clsFullSelectionChange

Private Sub UserForm_Initialize()
Set cFullSelectionChange = New clsFullSelectionChange
End Sub

Private Sub cFullSelectionChange_ChartSelected(cht As Chart)
Me.txtActiveChart.Text = cht.Name
Me.txtActivePivot.Text = ""
End Sub

Private Sub cFullSelectionChange_PivotSelected(pvt As PivotTable)
Me.txtActivePivot.Text = pvt.Name
Me.txtActiveChart.Text = ""
End Sub

Private Sub cFullSelectionChange_OtherSelected()
Me.txtActivePivot.Text = ""
Me.txtActiveChart.Text = ""
End Sub

The top of the UserForm module has the WithEvents declaration of the cFullSelectionChange class. It’s declared WithEvents because the class raises the pivot and chart activation events that we want to capture in this form. The form’s Initialize sub simply instantiates the
cFullSelectionChange class. Below that are the three event modules for pivot, chart or other selection.

The form instantiates the cFullSelectionChange class, which in turn instantiates a cChart class for each chart in every open workbook. Whenever a chart is activated its cChart class passes this action up to the cFullSelectionChange class, which raises the selection event in the UserForm.

Here’s a sample workbook with the form and code. Let me know what you think!

Track ActiveCell Movement Within Selection

Track ActiveCell Movement Within Selection

I searched the internet a bit before deciding that neither Excel or the forums have a way to track Activecell movement in a Selection. So, even though I don’t have the most pressing need for such an event, I spent several quality hours coding one.

The Issue
Here’s what I’m talking about. You can capture cell activation using Selection_Change code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Range("A2") = ActiveCell.Address
Me.Range("B2") = ActiveCell.Value
End Sub

Selection Change

But this doesn’t capture cell activation within a selection, the kind that happens when you select an area and then tab through it:

No ActiveCell Change

I noticed this while creating a little ActiveCell Viewer form. My solution is to capture TAB key presses using Application.OnKey. This is a little clunky in a couple of ways, but it seems to work.

My Solution
So my userform uses OnKey to capture the Tab press when a Selection has more than one cell. Now I know when the active cell is changing within a selection, so that’s good. The hard part is that, since I’ve killed the normal Tab behavior, I have to recreate it in VBA with code that moves the ActiveCell one cell forward. Before I can do that I need to define the active cell’s location in terms of the selection.

Basically, the code cycles through each cell in each Area of the Selection. The Areas cycle through in the order they were selected. Tab cycles through each cell of an Area from top to bottom, right to left no matter how you selected the Area.

A Little More About Areas
I do believe that areas are always rectangular. If you make a selection with the control key down, each down-drag-up of the mouse represents an area. I’ve delved into this area (hah) before in the SelectTracker post. As indicated there, if you hold down Ctrl and click a cell five times you get a Selection with five Areas. Interestingly, Excel 2016 gives the unsuspecting user a bit of a hint that this is happening by making the cell a little darker with each click:

darkening Selection

Back to the Coding Process

At first, as so often happens, the VBA seemed fairly simple:

  1. Find the ActiveCell’s position within the overall Selection by looping through its Areas and through each Area’s cells.
  2. Figure out what the next (or previous) cell would be, accounting for moving from one Area to the next and hopping to the beginning of the next Area or looping back to the beginning of the whole Selection.

Then I started thinking about Selections with multiple Areas that intersect the ActiveCell. To deal with this I did two things needed to make the tabbing flow smoothly through overlapping Areas.:

  1. Created a global variable to track which of these areas was selected by the last Tab press.
  2. Created a short routine to figure out the “most recent” Area that intersects the ActiveCell

The Code
Here’s the code that identifies the Activecell’s position within the Selection’s Areas and Cells:

Private Sub SelectNextCellInSelection()

'This sub is called when the Tab key is pressed.
'The point is to be able to capture Tab key presses
'that occur within a Selection, thereby capturing movement of the Activecell.
Dim SelectionCellIndex As Long
Dim SelectionCell As Excel.Range
Dim SelectionArea As Excel.Range

For Each SelectionArea In Selection.Areas
    SelectionCellIndex = 0
    'Mod lets us cycle to the Area after we reach the last one
    SelectionAreaIndex = (SelectionAreaIndex Mod Selection.Areas.Count) + 1
    For Each SelectionCell In Selection.Areas(SelectionAreaIndex).Cells
        SelectionCellIndex = SelectionCellIndex + 1
        If SelectionCell.Address = ActiveCell.Address Then
            GoTo SelectNext
        End If
    Next SelectionCell
Next SelectionArea

'Cycle to the next cell. If we're in the last one, we'll cycle to first.
SelectionCellIndex = (SelectionCellIndex Mod Selection.Areas(SelectionAreaIndex).Cells.Count) + 1
'If we're in the first cell must have gone to the next Area.
If SelectionCellIndex = 1 Then
    SelectionAreaIndex = (SelectionAreaIndex Mod Selection.Areas.Count) + 1
End If

'Because it will get incremented next time round
SelectionAreaIndex = SelectionAreaIndex - 1
End Sub

The code above uses the Mod function to cycle back to the beginning of the next Area or of the entire Selection.

The code to cycle backwards was surpisingly more difficult. Mod didn’t work in reverse, For/Next doesn’t work in reverse, and the beginning number is the last number of the previous Area changes (not 1). So although the logic was the same, the coding was much trickier.

Putting this in a UserForm had the additional complication that the procedures assigned to a key using OnKey have to reside in a regular module. So my code has two very short routines that poke a property in the UserForm, effectively passing the action back into the form as soon as possible.

I also learned VBA’s AppActivate command which shifts the focus from the form to Excel proper. I do that at the end of the forms Activate procedure, since it’s a Viewer after all and doesn’t need the focus.

The ActiveCell Viewer
You may be wondering why you even need an ActiveCell viewer. Well, you probably don’t. But if you did, it might be to see what’s in cells with big blobs of text, or ones that are in hidden columns or rows. And its ability to view cell contents using different format strings might also be nice:

Viewer in Action

Download the sample workbook and try it yourself!

Filtering Pivot Table Value Fields

Filtering Pivot Table Value Fields

In the old days, before I knew better, when I needed to filter a pivot table Value field I’d do it by using by throwing an autofilter from the Data menu on it:

Pivot with Autofilter

It made me feel dirty but I didn’t know any other way to filter pivot table value fields. Clearly pivot tables provided no filter in the Values columns:

Pivot with no filter

And then one day I read this SuperUser answer by mtone and never looked back. If you don’t know how to apply a Value Filter to a non-Value pivot field I recommend reading it right now:

SuperUser Value Filter answer

Good wasn’t it? I started to write a post about this and realized this was the best explanation I’ve seen. I especially like that mtone explains how choosing different fields to apply the filter to will result in different levels of aggregation, and will change your results.

One Value Field Filtering Oddity

I was prompted to write this post after answering Bijan’s question on Stack Overflow. I quickly helped solve his problem but was perplexed because his original issue was that he was applying a value filter to the value field itself. As I said at the beginning, this isn’t possible. Turns out that’s not quite true. After an extended chat he showed me how he did it.

Before you can do this, you first need to use the field as a Row, Column or Report field. Simply dragging it to the Row area of the Show Filter dialog and then to the Values area will do the trick. Once you do so, you can click the down arrow next to the field name in the pivot’s Show Fields dialog:

show fields with dropdown

At that point you can pick the various Value filters.

value field Value Filters

As Bijan discovered though this doesn’t actually do anything. No filtering occurs (and if it did, it wouldn’t be an aggregate filter anyways). And when you drag the field back to a non-value position it, the filter that is, goes away. Weird and bug-like.

MenuRighter Update Finished

MenuRighter Update Finished

A year or so ago I posted about updating MenuRighter, my free add-in for customizing Excel’s right-click menus. It’s done! You can go to the MenuRighter page for a download link and instructions. Or read on for some examples of what MenuRighter can do.

MenuRigher Customization Examples
Below I’ve modified the Row menu, with the default Excel 2016 version on the left and the modified version on the right. Copy, Cut, Paste and a few other buttons were removed and Freeze Panes is added to the top:

MenuRighter sample Row before


Here’s the modified Row menu in use. Note that the button caption changes in context from “Freeze” to “Unfreeze”:

MenuRighter Row modification in action

Modified Cell Menu
Here’s my modified Cell right-click menu. It’s barely recognizable, so let me restate that this is the menu you get when you right-click in a cell. I’ve again deleted the Cut, Copy, Paste, Clear Contents and several other buttons. At the top I’ve added the menu for my personal addin, cleverly named “myBar.” Just one click added the whole structure to the Cell menu:

MenuRighter sample Cell

You can see that I’ve also added a whole bunch of filtering buttons. Two of the built-in ones are pulled up a level from the Cell>Filter menu, like “Filter by Selected Cell’s Value.” There’s also a couple for my own routines, like “Filter by All Selected Values.”

“Autofilter” is there so I can toggle filtering for whatever Table or range I’m in. (If you’re looking for this one, one its locations is Worksheet Menu Bar > Data > Filter > AutoFilter).

Two Other Examples

Here I’ve added a couple of my routines to the Ply menu. That’s the one you get by right-clicking a sheet tab:
MenuRighter sample Ply

And here’s the entire File menu added to the Cell menu. Everything at your fingertips!

Cell with File

Just kidding. I’d never do that.

New MenuRighter Page

Again, here’s the page with the download link and instructions for the new version. Please let me know what you think if you try it.

Editing Shape Points for No Good Reason

Editing Shape Points for No Good Reason

Over at Bacon Bits, Mike Alexander has a nice post out yesterday on editing shape points to create custom graphics. It shows how to use a shape’s Edit Points command to create interesting dashboard icons. I haven’t used Edit Points for years, but his post inspired me to fool around with them. I ended up with a bit of code for Editing Shape Points for No Good Reason.

In Mike’s post he shows how you can edit points to modify a half-circle into a more interesting shape. His edit of a half-circle looks something like this (only better):

semicircle to wavy

I thought that was pretty cool, so then I dragged the top below the bottom:

semicircle to wavy

That was fun. Now how about some facial reconstruction for Smiley?

smiley to pointy

At this point of course I had to learn how to program these edits in VBA. The result is code that randomly messes with the edit points, along with colors:

Pointless Point Editing Code

Sub EditPointMadness()
Dim shp As Shape
Dim shpNodes As ShapeNodes
Dim CenterX As Long
Dim CenterY As Long
Dim CurrXValue As Long
Dim CurrYValue As Long
Dim ws As Excel.Worksheet
Dim pointsArray As Variant
Const PointOffset As Long = 200

Set ws = ActiveSheet
If ws.Shapes.Count = 0 Then
    ws.Shapes.AddShape msoShapeSmileyFace, 300, 300, PointOffset, PointOffset
    Exit Sub
End If
Set shp = ws.Shapes(1)
CenterX = shp.Left + (shp.Width / 2)
CenterY = shp.Top + (shp.Height / 2)
Set shpNodes = shp.Nodes
With shpNodes
    .Insert WorksheetFunction.RandBetween(1, .Count), msoSegmentCurve, msoEditingAuto, _
        WorksheetFunction.RandBetween(CenterX - PointOffset, CenterX + PointOffset), _
        WorksheetFunction.RandBetween(CenterY - PointOffset, CenterY + PointOffset), _
        WorksheetFunction.RandBetween(CenterX - PointOffset, CenterX + PointOffset), _
        WorksheetFunction.RandBetween(CenterY - PointOffset, CenterY + PointOffset), _
        WorksheetFunction.RandBetween(CenterX - PointOffset, CenterX + PointOffset), _
        WorksheetFunction.RandBetween(CenterY - PointOffset, CenterY + PointOffset)
    If Timer Mod 2 = 0 Then
        pointsArray = .Item(WorksheetFunction.RandBetween(1, .Count)).Points
        CurrXValue = pointsArray(1, 1)
        CurrYValue = pointsArray(1, 2)
        .SetPosition WorksheetFunction.RandBetween(1, .Count), _
            CurrXValue + WorksheetFunction.RandBetween(-PointOffset, PointOffset), _
            CurrYValue + WorksheetFunction.RandBetween(-PointOffset, PointOffset)
        shp.Fill.ForeColor.RGB = WorksheetFunction.RandBetween(1, 10000000)
        shp.Line.ForeColor.RGB = WorksheetFunction.RandBetween(1, 10000000)
    End If
    If Timer Mod 5 = 0 Then
        .Delete WorksheetFunction.RandBetween(1, .Count)
    End If
End With
End Sub

Every time you run the code above it adds, deletes and/or modifies another point. After a couple of times you get what I like to call “Picasso Smiley”:

smiley picasso

A few more and Smiley is getting blown into the next dimension:

smiley in the next dimension

Hopefully the code above is pretty straightforward. It leaves a few of the settings unrandomized, chiefly whether the new node is straight, shaped or a corner.

One question. How to refresh Excel between shape format changes?
One version of this code had a loop that edited the points every half second. But try as I might I couldn’t get the screen to update and show those changes. The changes would only appear after the code was finished, making a loop pointless. If anybody knows how to do this, please let us know (thereby adding some useful content to this post).

Here’s a workbook with the the code and a couple of buttons to run it.

smiley edit points