how to annotate pivot tables?

[miconian]

My users spend a lot of time in large pivot tables that are set up in compact view, expanding and collapsing fields by clicking on the plus and minus symbols. As they do this, they want to make notes about individual line items.

However, this is difficult because a) there is nowhere that allows the user to make notes inside the pivot table, and b) if they make notes outside the table, the row their note corresponds to will change when fields are collapsed and expanded. Also, it seems that Excel does not allow comments to be added to cells within pivot tables.

Surely others have had this problem. Is there some obvious workaround I'm missing?

thanks!

 

[Jerry Sullivan]

Regarding the message about replacing values, that's a normal Excel warning that occurs when an update to the PivotTable is going to overwrite data. If that's happening when you unfilter, are you changing the Column filters in a way that makes the PivotTable use more columns (overwriting the comments). You can probably just ignore that if the VBA code restores the comments to the right of the updated PivotTable's location.

I'm not following your comment about repeating all notes. Please explain that in more detail.

 

[BORUCH]

Regarding the message about replacing values, that's a normal Excel warning that occurs when an update to the PivotTable is going to overwrite data. If that's happening when you unfilter, are you changing the Column filters in a way that makes the PivotTable use more columns (overwriting the comments). You can probably just ignore that if the VBA code restores the comments to the right of the updated PivotTable's location.

I'm not following your comment about repeating all notes. Please explain that in more detail.

Yes, the filtering and un-filtering will make more columns or lese columns be displayed

Is there a code that you can give me that said if I get that error message to want to replace it should just say ok

 

[OLGGRV]

Jerry, I would like to thank you for taking time to write this code, sharing it and even improving it on demand, when requested by other users. You did an amazing job - it works perfectly!
I am really new to VBA programming (hope I'm even using the right terminology , but this experience inspired me to learn at least some of it...
Having said that, I was able to make some minor changes to the code - such as renaming comment 1 and comment 2 columns, but I can't figure out how to include the grand total row to the range in the code. As I would like the grand total row, the last one the Pivot table, to have comment as well. Is it possible? Could you please share what I need to do to get it to work?

 

[Quinn1992]

Hi Jerry - I'm getting the following message when trying to execute this code on my pivot table "'Compile Error: Ambiguous name detected. Check Setup'. I;m trying to run the code from #55 and I'm using Excel 2016. Any help would be very welcome.

 

[Jerry Sullivan]

Hi Jerry - I'm getting the following message when trying to execute this code on my pivot table "'Compile Error: Ambiguous name detected. Check Setup'. I;m trying to run the code from #55 and I'm using Excel 2016. Any help would be very welcome.

Hi Quinn1992, That error message indicates that you've copied the procedure "Check Setup" to two places within the VBA Project. You should only have one procedure with that name and it should be located in a Standard Code Module (like Module1, not a Sheet Code module).

 

[Quinn1992]

Hi Quinn1992, That error message indicates that you've copied the procedure "Check Setup" to two places within the VBA Project. You should only have one procedure with that name and it should be located in a Standard Code Module (like Module1, not a Sheet Code module).

Thanks Jerry - The file is creating the two notes columns as expected now, however when I enter in notes and filter the pivot table those notes are copied into every field. My pivot was created using power pivot, I see you suggested using the code in post #41 as a workaround but this isn't triggering any actions after I've entered these VBA into the different module and sheet.

 

[Jerry Sullivan]

Thanks Jerry - The file is creating the two notes columns as expected now, however when I enter in notes and filter the pivot table those notes are copied into every field. My pivot was created using power pivot, I see you suggested using the code in post #41 as a workaround but this isn't triggering any actions after I've entered these VBA into the different module and sheet.

Quinn1992, You described two different problems: "notes copied into every field" and "isn't triggering any actions".

Are you encountering both those problems after using the code in Post #41, or just the 2nd one?

For the problem of the actions not being triggered, one possibility is that events became disabled. If the events don't seem to be triggering, run this macro. It will enable events and also let you know whether or not they were already enabled.

Sub EnableEventsNow()
    Dim sMsg As String
    With Application
        sMsg = IIf(.EnableEvents, _
            "Events were already enabled.", _
            "Events are now enabled.")
        .EnableEvents = True
    End With
    MsgBox sMsg
End Sub

Also check to make sure that the "Sub Worksheet_Change" code is in the Sheet Code module of the Sheet with the PivotTable. If you've placed that in a Standard Code module like Module1, the events won't be triggered.

 

[quynhi]

Hi Jerry,

Thank you for spending your time to help us.

I run into issue, every time I refresh powerpivot, the excel shut down and reopen with saved version. The report only need one column, using tubular and compact mode. Please help!!!

Best regards,

Q

 

[Jerry Sullivan]

Hi Q, I'd suggest you start a new thread for that question.

 

[pvanerk]

Hi,

First of all, you've done a great job Jerry. Thanks for all your time and effort!

I also had some problems with dates used in PivotTable columns. In the kKeyPhrase the dates are stored as serial numbers. The VBA code retrieves the date as a Date string (for example "24-01-2017"). The solution to use SourceName didn't work for me.

I made the following change to the code:

Private Function GetKey(rPC As Range, vFields As Variant) As String
    Dim i As Long
    Dim sNew As String
    
    With rPC.PivotCell.RowItems
        For i = LBound(vFields) To UBound(vFields)
            If i > .Count Then sNew = "" Else sNew = .Item(i).Caption
[B]               If IsDate(sNew) Then sNew = CLng(DateValue(sNew))[/B]
            GetKey = GetKey & sNew & "|"
        Next i
    End With
 End Function

So I am checking the values for dates and convert them to long.

I hope this can help someone.