MenuRighter Now 64-bit Compatible

MenuRighter Now 64-bit Compatible

I’ve updated MenuRighter, and it now works with 32-bit and 64-bit versions of Office 2010 through 2016. I’ve had a few requests for a 64-bit version, so hopefully this will help some folks.

If you’re not familiar, MenuRighter is my addin that allows extensive customization of your right-click menus.There’s lots of information on it at the page linked below.

The download at the following page now works with both 32-bit and 64-bit versions of Excel 2010 through 2016. Please let me know if you have any questions or find any issues with it:

MenuRighter 2010 and Later

MenuRighter v2 in repose

Vote for Me! (Please)

Vote for Me! (Please)

Mike (Mr. DataPig) Alexander is having a contest – Create a Dashboard in Five Minutes. I procrastinated and slipped in just under the deadline – Sunday evening after finishing my chores I figured out how to use Screencast-O-Matic for more than just animated gifs. I turned on my mic, took the black tape off my camera and recorded a five-minute screenshot. It took about thirty takes (as I mentioned to Mike I now have even greater respect for Orson Welles and Aaron Sorkin). I came away with a video that starts with some unnormalized baseball stats and ends up with two pivots and a slicer. Along the way I showcase a few of my own tools, some of which I’ve blogged about. It was fun, I love learning new things fast.

There’s just one problem, dear reader. So far my video only has two measly votes, despite Mike calling me “stately” and saying that this very blog is one of his favorites (and despite voting for myself):

My dashboard video

Now, you know I don’t ask you for much. So, if you’ve ever said to yourself, I don’t know, anything nice about this blog, or MenuRighter, or are just feeling randomly generous, click the link below, follow it to YouTube and vote for me, yoursumbuddy.

After that you should watch some of the other videos.

Determining if a Pivot Field Has Visible Subtotals

Determining if a Pivot Field Has Visible Subtotals

In my last post I talked about identifying pivot table’s Values field, if it had one. That function plays a part in this post, which is shows two functions for determining if a pivot field has visible subtotals. As with the last post, I didn’t find much about this on the web. I even asked my first Excel question on Stack Overflow. After a bunch of experimentation I came up with a function that seems to always work. And then, whaddaya know I came up with a better one. I use the second function in my improved per-item conditional formatting utility, which I will post about soon.

What Do I Mean by “Visible Subtotals?”

In the picture below the pivot table is set to show subtotals for every field. However subtotals are actually visible only for the Region field. There’s none for the Items field, which makes sense since Items is the rightmost field, and its subtotals would just be a repeat of the individual item values:

All Subtotals at Bottom

The VBA Subtotals Property Does Half the Job

The first thing you might try in VBA is checking the pivot fields Subtotals property. However below you can see that it returns True for both fields. The issue is the same as above: Subtotals are turned on but they don’t show for the rightmost field:

Subtotals in Immediate Window

My First Attempt

So, I wrote some code that:
1. Checks if a field’s subtotals are turned on. If not, the function returns False.
2. Checks if any fields with the same orientation as the field we’re checking is a Values field
3. Tests if the field we’re checking is in the last position for its orientation (including the Values field). If not, then subtotals are on and the function returns True.

Function PivotFieldSubtotalsVisible_OLD(pvtFieldToCheck As Excel.PivotField) As Boolean
Dim i As Long
Dim SubtotalsOn As Boolean
Dim pvt As Excel.PivotTable
Dim ValueField As Excel.PivotField
Dim FieldPosition As Long

With pvtFieldToCheck
   'Only row and column fields can show subtotals,
  If Not (.Orientation <> xlColumnField Or .Orientation <> xlRowField) Then
      GoTo exit_point
   End If
   Set pvt = .Parent
   
   'Get the pivot tables ValuesField
  Set ValueField = GetValuesField(pvt)
   'The Value field is a column or row field,
  'but won't have subtotals
  If ValueField Is pvtFieldToCheck Then
      GoTo exit_point
   End If
   
   'There are 12 possible types of Subtotals (at least XL 2003 on)
  'If any of them are TRUE then Subtotals are on.
  For i = LBound(.Subtotals) To UBound(.Subtotals)
      If .Subtotals(i) = True Then
         SubtotalsOn = True
         Exit For
      End If
   Next i

   'No need to proceed if they aren't even on
  If Not SubtotalsOn Then
      GoTo exit_point
   End If
   
   FieldPosition = .Position
   'This is confusing, but
  'if the Values field's position is greater than the field-to-check's position
  'we want to ignore the Values field, as it won't affect the field-to_check's visibility
  If Not ValueField Is Nothing Then
      If ValueField.Orientation = .Orientation And ValueField.Position > FieldPosition Then
         FieldPosition = FieldPosition + 1
      End If
   End If
   'If the field-to-check isn't in the last position
  '(taking into account the Values field)
  'then it's Subtotals will be visible
  If (.Orientation = xlColumnField And pvt.ColumnFields.Count > FieldPosition) Or _
      (.Orientation = xlRowField And pvt.RowFields.Count > FieldPosition) Then
      PivotFieldSubtotalsVisible_OLD = True
   End If
End With

exit_point:
End Function

A Better Way – PivotCell to the Rescue

The above seems to work fine, but it’s got kind of a feel-your-way-in-the-dark aspect to it. I would much rather just have some code that examines the actual pivot table and figures out whether a given field is currently showing any subtotals. Happily, I have found a way to do this.

It’s based on the Range.PivotCell object and its PivofField and PivotCellType properties, all of which go back to Excel 2003, according to this MSDN page. They allow you to cycle through a pivot table’s cells checking for ones with a PivotCellType of xlPivotCellSubtotal (or xlPivotCellCustomSubtotal ) and, if so, checking what PivotField the subtotals belong to. I’ll discuss this some more after the VBA.

The Code

Function PivotFieldSubtotalsVisible(pvtFieldToCheck As Excel.PivotField) As Boolean
Dim pvt As Excel.PivotTable
Dim cell As Excel.Range

With pvtFieldToCheck
   'Only row and column fields can show subtotals,
  If Not (.Orientation = xlColumnField Or .Orientation = xlRowField) Then
      GoTo exit_point
   End If
   Set pvt = .Parent
   For Each cell In Union(pvt.ColumnRange, pvt.RowRange)
      If cell.PivotCell.PivotCellType = xlPivotCellSubtotal Or cell.PivotCell.PivotCellType = xlPivotCellCustomSubtotal Then
         If cell.PivotCell.PivotField.Name = .Name Then
            PivotFieldSubtotalsVisible = True
            GoTo exit_point
         End If
      End If
   Next cell
End With

exit_point:
End Function

How it Works

The code above actually only checks the pivot table’s ColumnRange and RowRange. These ranges are highligthed in the picture below. The code checks this area for cells with a PivotCellType of subtotal or custom subtotal. There are 10 PivotCellTypes, nine of which can be found in the ColumnRange or RowRange areas (the data area of the pivot table consists just of the xlPivotCellValue type.

ColumnRange and RowRange

The picture below highlights the cells with a PivotCellType of either xlPivotCellSubtotal or xlPivotCellCustomSubtotal. The custom subtotals are ones such as Min, Max and Average. These can be set in the field options menu. If the code finds a cell whose PivotCell.PivotCellType property is one of these two it then checks the cell’s PivotCell.PivotField object for a match with the field passed to the function.

PivotCell Subtotal types

I Like PivotCells

I’ve used the Range.PivotTable object quite a bit over the years. But it’s just recently that I’ve delved into the Range.PivotCell property. Hopefully I’ve given you some ideas for how you could use it to poke around in pivot tables.

Have you used the Range.PivotCell property? If so, leave a comment (I also love comments, especially the ones that add to my knowledge and don’t require me to do anything but say “thanks”).

PivotItem.DataRange Off By One Row Bug

PivotItem.DataRange Off By One Row Bug

This week I ran into a pivot table VBA issue I’ve never noticed before. When a pivot table has more than one data field, referring to a PivotItem.DataRange returns a range one row down from where it should be. Below you can see that the PivotItem.DataRange address is one row off and that the selection is below the pivot table:

PivotIItem.DataRange Offby One

If the pivot table has only one data field, e.g., if I get rid of “Sum of Total” above, the issue goes away.

I found one reference to this by Macro Marc on SO, but nothing else on the web. It seems like it would be a well-known thing though, especially if it’s been around for a while.

I’m curious if anybody knows whether this has been reported as a bug. I noticed it on my home computer running Office 365 Pro Plus. I’d be interested to hear if it’s on other versions.

My Workaround

In my very limited testing it seems like there isn’t a similar issue for PivotFields. So one idea is to compare the first row of a pivot field against the first row of its first pivot item and use the difference, if any, to offset the PivotItem.DataRange back to where it should be. However, I’m not sure that my concept of “first” will always be the same as Excel’s. Anyways I’m using this function:

Function GetPivotItemOffsetBugCorrection(pvt As Excel.PivotTable) As Long
'Only occurs if the pivot table has more than one data field
If pvt.DataFields.Count = 1 Then
   Exit Function
End If

GetPivotItemOffsetBugCorrection = pvt.VisibleFields(1).DataRange.Row - _
    pvt.VisibleFields(1).VisibleItems(1).DataRange.Row
End Function

Then I use it like this in places where I refer to a pivot item’s data range:

Set pvt = pvtItem.Parent.Parent
PivotItemOffsetBugCorrection = GetPivotItemOffsetBugCorrection(pvt)
For Each cell In pvtItem.DataRange.Offset(PivotItemOffsetBugCorrection)

Yuck!

If you’ve got a good solution for dealing with this, or any info, please leave a comment.

Identify a Pivot Table’s Values Field

Identify Pivot Table Values Field

Over the next few posts I plan to delve into a couple of functions I’ve written to identify areas in a pivot table. I also want to do a quick post on a pivot quirk I noticed recently. I then plan to roll it up into a post on my new-and-improved per-pivot-item conditional formatting tool. It’s good to have plans, right? Anyways, let’s get started with a function to identify a pivot table’s Values field.

I deduced the following just by messing around – I couldn’t find anything on the web about identifying a Values field. If I got something wrong, or if you have a better way to do this, please leave a comment.

What is a Values Field?

The Values field is the one that appears when you have more than one data field. Its location in the Rows or Columns area of the pivot table dialogs controls the grouping of those data fields. In the following example, I’ve grouped the data area by data fields within years. In other words, the two summing data fields appear side-by-side for each year:

Values Field by years then values

In the next example I’ve dragged the Value field up and now the data area grouping is for years within data fields:

Values Field by values then years

Some pivot table layouts, such as the one below, don’t show the word “Values” anywhere in the pivot table, but it still shows in the pivot table dialog:

Values Field Column Labels

Like all pivot fields, the Values field can be renamed. Note that though I changed it to “Frodo” in the pivot table, it still says “Values” in the dialog:

Values Field by values called Frodo

Everything I’ve said about the Columns area of the pivot dialog applies to the Rows area. The Values field behaves the same way there.

Identifying the Values Field in VBA

So, anyways, I wanted a VBA function that returns a pivot table’s Values field if it has one. When figuring out how to do this I asked myself:

Is the Values field a PivotTable.DataField or a PivotTable.ColumnField/RowField?

The answer is both, kind of. So, for instance, in the examples pictured above typing the following into the immediate window returns “Values”:

? ActiveCell.PivotTable.ColumnFields("Values").Name

And so does this:

? ActiveCell.PivotTable.DataFields("Values").Name

So it looks like the Values field is both a data and column (or row) field. To further confirm this, note that this statement returns True:

? ActiveCell.PivotTable.DataFields("Values").Orientation = xlColumnField

So, even though it’s both a Data and Column (or Row) field it looks like it’s a bit more of a Column field (I’m going to stop saying “or Row” now). This is backed up by the fact that you can’t refer to it’s Data personality using an index. In other words, the following returns an error:

? ActiveCell.PivotTable.DataFields(3).Name (1 and 2 return the two other data fields)

Furthermore, if you check the DataFields.Count for the example above the count is only two.

Cutting to the Chase

In addition to the above, I’ve got one more informational tidbit: if you change the name of the Values field to “Frodo,” both its Data and Column selves refer to themselves as “Frodo.” So even though, as we’ve seen above, the dialog box continues to use the word “Values” to refer to this field, ? ActiveCell.PivotTable.DataFields("Values").Name gets you a runtime error 1004.

This means that you can’t just refer to the values field using “Values” in either its DataField or ColumnField version. If you do and a user changes its name you’re out of luck.

Fortunately, this has an upside, and it’s not just that I have something to blog about. It means that a Values field name is the only field name in the pivot table that can be repeated for a Data field and a Column field. Usually two fields can’t have the same name. For example, in the examples above if you try to rename “Year” or “Values” to “Sum of Unit Cost” you’ll get a “Field name already exists” error. But in the case of a Values field both its Data and Columm/Row references will be the same name.

This means you can identify a pivot table’s Value field by finding a row or column field that has the same name as a data field. Cool, eh?

The Function

Function GetValueField(pvt As Excel.PivotTable) As Excel.PivotField
Dim pvtField As Excel.PivotField
Dim TestField As Excel.PivotField
Dim ValueField As Excel.PivotField
 
'If there's only one data field then there won't be a Values field
If pvt.DataFields.Count = 1 Then
    GoTo exit_point
End If
 
For Each pvtField In pvt.PivotFields
    On Error Resume Next
    'test each non-data field for a data field with a matching name
   Set TestField = pvt.DataFields(pvtField.Name)
    On Error GoTo 0
    If Not TestField Is Nothing Then
        'if there's a match then you've got the Values field
        Set ValueField = pvtField
        Exit For
    End If
Next pvtField
Set GetValueField = ValueField
 
exit_point:
End Function

Boom! Let me know if you’ve got a better way, anything to add, etc. And, as always, thanks for dropping by.

Force Userform Textbox Scrolling to Top

Force Userform Textbox Scrolling to Top

I use my Edit Table Query utility every day to easily modify and test SQL in Excel. The main textbox contains the SQL code, which often fills more than the textbox. The problem is when I click into the textbox it always scrolls to the bottom. Even though this has been happening for months this always catches me off guard. I’m surprised, then annoyed. I finally decided to take action, and came up with some code to force userform textbox scrolling to the top.

The Issue
Here’s an example of what I’m talking about. When I click the New Data button the textbox content looks good, in that the numbers start at one. But as soon as I click into it the content scrolls to the bottom. (To add to my annoyance, the scrollwheel doesn’t work in the textbox.)
textbox scrolling issue

My solutions uses the Textbox’s SelStart and SelLength properties. I set both to 0, meaning that the selection starts before the first character. That’s what the “Force Start at Top” checkbox in the form does. (Download below!)

However, when I added those two lines of code another issue appeared. There was no scrollbar. In fact in the animation above you can see that there’s no scrollbar until I click into the textbox. And below you can see that with the scrolling fix applied there is no scrollbar:

no scrollbar after fix

You can force the scrollbar to appear by arrowing down past the bottom of the visible content. An internet search came up with the solution of setting focus on the textbox. I do this before applying the SelStart/SelLength code. That’s what the “Make scrollbars visible” checkbox does:

textbox scrolling fixed

VBA
Here’s a basic subroutine that takes some text and a button object as parameters. It sets a textbox’s text, sets the focus on the textbox, sets the selection start to zero and sets the focus back to the calling button.

Sub FillTextboxText(TextboxText As String, CallingButton As MSForms.CommandButton)

Me.TextBox1.Text = TextboxText
Me.TextBox1.SetFocus
Me.TextBox1.SelStart = 0
Me.TextBox1.SelLength = 0
CallingButton.SetFocus
End Sub

Other Stuff

Note that the issue with the scrollbar not appearing only occurs once in the life in the userform. In other words, once it has appeared it will always appear. I think.

You might have noticed that the form also has a Same Data button, this button simply saves the textbox contents to a string variable and then set the textbox’s text to that variable. Oddly, when you do this and then click into the textbox no scrolling happens at all, even before the checkboxes are checked. To see this, leave the checkboxes unchecked, click Restart, then click New Data, then scroll halfway up and then click Same Data. There’s no scrolling, even though I’ve done almost the same thing as was done with the New Data button.

This all makes me wonder how MS programmed textbox behavior. It seems almost like it forces the textbox to the bottom to make the scrollbar appear, and that it somehow checks the contents before it changes the scrolling position.

Download

Here’s a workbook with the Userform shown in this post.

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
Else
    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", " ", "_")
Else
    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

Exit_Point:
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:

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

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.

Download
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!