Modify PivotTable double-click behavior --OR-- ...

[gr8tday]

Hello, I need some VBA code to address the following:

Scenario: A pivot table reveals a probable error in the underlying source data. The error needs to be located and corrected in the source data, and then the pivot table needs to be refreshed.

*Problem*: The double-click action on a pivot table fields creates a new worksheet with the records applicable to that field. A user must *identify the data error in the new worksheet and then scour the source data to find that same record*, correct the data error in the source, and refresh the pivot table.

One of two alternatives would make this process much easier for the user:

Option 1: Use VBA to modify the behavior of a PivotTable cell's double-click action to return the user to the source table, and automatically apply filters to the source data to display only those the records applicable to that field. (Assume the source data is already formatted as a Table.) In other words, don't create a new sheet with the applicable data, just filter the source data to show the applicable data. Then the user can find and correct the problem in one shot - in the source data.

This option would mean the user would not have to manually delete the new worksheet when they are done with it - always an annoyance. However, I don't want to affect the new worksheet functionality to achieve this goal; I've seen other code for the PivotTable double-click function affect this functionality.

Option 2: Allow the PivotTable double-click to work as normal. When the user finds the culprit record in the new worksheet, automatically take the user to that same record in the source table via a a macro short-cut key.

Thank you for any assistance you are able to provide!

 

[David Wayne]

Good morning Jerry,

It totally worked! I can't believe it. It is amazing so much code goes into executing what seems like a simple task.

My next issue is all of the date fields are formatted to general (eg 41927 - excels numerical date format) and I know it is easy to format to date, but I dont expect my bosses to do so or even know how.

So is there a way in the code to specify formatting of dates?

I clearly need to learn vb. Do you know of an online class or book I could buy that you would recommend?

Thanks again and I may need to ask you something else in the future.

David

 

[Jerry Sullivan]

David, I'm glad to hear you were able to adapt that code. Since this is a somewhat different topic that the OP for this thread, I've provided some code that responds to your question about date formatting in that other thread that I referenced.

http://www.mrexcel.com/forum/excel-...olumns-range-instead-table-2.html#post4058314

I think that will be useful to people who read that other thread, and less confusing for those that read this thread.

I clearly need to learn vb. Do you know of an online class or book I could buy that you would recommend?

Regarding learning VBA for Excel, there's no shortage of information available on the topic. Since each person learns differently I recommend using whatever method or tools have worked well for you when you've learned other technology.

Some people learn best from a tutorial book or taking a class; while others do best experimenting on their own using the resources like the Excel Developer Reference (Excel VBA Help) and online examples (that's how I learned VBA).

If you learn well from books, I think John Walkenbach's "....Power Programming with VBA" is a good one.

 

[vulcano]

Hello Jerry,

this code works perfect. My only failure was that the source was not formatted as table, only as range. So it couldn't work.

Many thanks

Valerij

 

[Jerry Sullivan]

Hi Valerij,

We're you able to modify the code to make it work or do you want some help?

The code example at the beginning of the thread assumed the source was a standard range. The modification in Post #17 allows the code to work with either a range or Excel Table data source. So I'm not sure why that didn't work for you with a standard range source.

 

[ellenb123]

Hi Jerry,

I have tried using this code with my own data set (including the change you mention later in this thread) but whenever I double click on an individual piece of data a message box appears saying "Run-Time Error #438:
Object doesn't support this property or method" and the debugger says that the problematic line of code is:
vUniqueIdVals = Application.index (rDrillDown, 0, vUniqueIdCol)

I am also using Excel 2013 and so do not understand where this error is coming from?

Thanks,

Ellen

 

[Jerry Sullivan]

Hi Ellen,

If you'll do some checks to get some more information, it will help to diagnose the problem.

When the code errors and takes you to the VBA editor, enter these expressions in the Immediate Window and post back the response that is returned.

?rDrilldown.Address

?vUniqueIdCol

The first value returned should be the address of the entire range of drilldown data
The second value returned should be the column number of the field that is to be used as unique identifiers.

In my example, that field was assumed to have the header "Index". If your field with the unique identifiers has a different header name, you'll need to modify this line of code:

Const sUNIQUE_ID_HEADER As String = "Index"

 

[ellenb123]

Hi Jerry,

Thanks for your help, I think my mistake was that I had replaced the word index with my header name in all lines of code rather than in just the line you mentioned above. It seems to work fine now!

My data set contains merged cells so that for some columns there are sub-rows within each, however when I run the above code only the top sub-row for each row is displayed rather than all of them. Is there way of all sub-rows being shown from the original data set when I run the code?

Thanks

Ellen

 

[Jerry Sullivan]

Hi Ellen, Merged cells can cause a lot of problems. It's best to not to use merged cells at all within ranges that will be used for calculations or as a data source.

If you really feel that you need to use merged cells to get the look you want for a report, presentation or user interface, then it's better to separate that "front-end" from the layer of workbook that handles data and calculations.

 

[ellenb123]

Ok, thanks for all your help!

 

[ankushskapoor]

Hi Jerry,

The solution provided by you for the drill down via double clicking the pivot to go to the source data worked well for me. But I have to go one step further.

I have a sheet that has charts from different pivot tables. Now is it possible to drill down by double clicking the chart. I have been able to find some code to double click the chart (by moving the chart from being an object in the pivot sheet to a new chart sheet), but this also drills down to the data by creating a new sheet each time. Can I somehow use your solution for the table to the chart.

Having achieved that in a stand alone chart sheet, how will I be able to create this chart if this chart is an object in a dashboard (multiple charts with slicers). I am on Excel 2010.

Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)


On Error GoTo ErrHandler

'Don't bother with clicks on anything other than chart series
If ElementID <> 3 Then Exit Sub

'Sometimes Excel interprets a double click as applying to the entire series instead of a single point.
If Arg2 = -1 Then
If Me.SeriesCollection(Arg1).Points.Count > 1 Then
If MsgBox( _
"Excel selected the entire series." & vbCr & _
"To see entire series, press Enter/click OK." & vbCr & _
"To see a single point, press Escape/click Cancel " & vbCr & _
"then click on the point once before double clicking.", _
vbOKCancel, "Selection problem") = vbCancel Then Exit Sub

End If
Arg2 = Me.SeriesCollection(Arg1).Points.Count + 1
End If

'Show the Pivottable cell's detail
'Me.PivotLayout.PivotTable.DataBodyRange.Cells(Arg2, Arg1).ShowDetail = True

ErrHandler:


If Err.Number <> 0 Then MsgBox _
"Chart_BeforeDoubleClick - Error#" & Err.Number & vbCrLf & _
Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
On Error GoTo 0

End Sub