how to annotate pivot tables?

miconian

Well-known Member
Joined
Aug 18, 2004
Messages
769
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!
 
This code is very useful. Microsoft should have installed this for the PT.
I am already enjoying the code from Jerry, thank you very much.

I'm having the same problem as Kgettings, as my notes would change to value when column expand. Without having any VBA knowledge, I had improvise by inserting additional column on the notes to allow PT to expand without overlapping onto my notes. The control is when you are expanding the PT column, excel will ask whether to allow overwrite the notes or not.
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi faisalhafifi,

Thank you for your feedback. It's nice to hear the code is being used. :)

I've been intending to do a comprehensive rewrite of the code. Until I get around to that, I'll try to improve the current version as people identify bugs or limitations.

To prevent the notes from being lost when a resized PivotTable overlaps the notes range...

Add this function to the Standard Code Module...
Code:
Public Function isPivotCell(rCell As Range) As Boolean
    On Error Resume Next
    isPivotCell = Not (IsError(rCell.PivotCell))
End Function

Replace the previous Worksheet_Change code with this procedure...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'----When changes are made in the named range displaying
'--    PivotTable Notes, the Note database table
'--    will be updated with each New or Revised note.

 
    Dim rNotesChanged As Range, c As Range
    Application.ScreenUpdating = False
    If Check_Setup(Me) = False Then GoTo CleanUp
    Set rNotesChanged = Intersect(Target, _
        Range(sRngName))
    If rNotesChanged Is Nothing Then Exit Sub

    For Each c In rNotesChanged
        '---Update each changed note cell unless the change was due to
        '      being overlapped by a resized PivotTable
        If Not isPivotCell(c) Then _
            Call Update_Note_Database( _
                PT:=Me.PivotTables(1), _
                rNote:=Intersect(c.EntireRow, Range(sRngName)))
    Next c
CleanUp:
    Set rNotesChanged = Nothing
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
Jerry,

This is awesome. Exactly the fix I was looking for. I am only left with on problem. When I add a comment to the bottom most row on the "comment" column on any version of your code that comment is also copied on the row below (outside of the pivot table). Try it and see if you see this glitch.

Thanks!

Osvaldo

Hi faisalhafifi,

Thank you for your feedback. It's nice to hear the code is being used. :)

I've been intending to do a comprehensive rewrite of the code. Until I get around to that, I'll try to improve the current version as people identify bugs or limitations.

To prevent the notes from being lost when a resized PivotTable overlaps the notes range...

Add this function to the Standard Code Module...
Code:
Public Function isPivotCell(rCell As Range) As Boolean
    On Error Resume Next
    isPivotCell = Not (IsError(rCell.PivotCell))
End Function

Replace the previous Worksheet_Change code with this procedure...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'----When changes are made in the named range displaying
'--    PivotTable Notes, the Note database table
'--    will be updated with each New or Revised note.

 
    Dim rNotesChanged As Range, c As Range
    Application.ScreenUpdating = False
    If Check_Setup(Me) = False Then GoTo CleanUp
    Set rNotesChanged = Intersect(Target, _
        Range(sRngName))
    If rNotesChanged Is Nothing Then Exit Sub

    For Each c In rNotesChanged
        '---Update each changed note cell unless the change was due to
        '      being overlapped by a resized PivotTable
        If Not isPivotCell(c) Then _
            Call Update_Note_Database( _
                PT:=Me.PivotTables(1), _
                rNote:=Intersect(c.EntireRow, Range(sRngName)))
    Next c
CleanUp:
    Set rNotesChanged = Nothing
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Jerry,

I apologize the error I described above only applies to the code where you first introduced 2 columns. I have modifies that one to work with hyperlinks:
Code:
Option Explicit
 
Public Const sRngName = "PT_Notes"
Public Function Check_Setup(ws As Worksheet) As Boolean
    Dim rNotes As Range, i As Long, bCompact As Boolean
    Dim PT As PivotTable, ptField As PivotField
    Dim tblNotes As ListObject
    Dim wsSave As Worksheet
 
'---Check if not exactly one PT on Worksheet- exit
    If ws.PivotTables.Count <> 1 Then GoTo StopNotes
    Set PT = ws.PivotTables(1)
 
'---Check Compact Layout option is same for all fields (True or False)
    Select Case PT.RowFields.Count
        Case 1: 'do nothing
        Case 0: GoTo StopNotes
        Case Else
            bCompact = PT.RowFields(1).LayoutCompactRow
            For i = 2 To PT.RowFields.Count
                If PT.RowFields(i).LayoutCompactRow <> bCompact _
                    Then GoTo StopNotes
            Next i
    End Select
 
'---Check if Named Range "PT_Notes" doesn't exist- define it
    If Not NameExists(sRngName, ws.Name) Then
        With PT.TableRange1
            Set rNotes = Intersect(PT.DataBodyRange.EntireRow, _
                    .Resize(, 2).Offset(0, .Columns.Count))
        End With
        Set rNotes = rNotes.Resize(rNotes.Rows.Count _
            + PT.ColumnGrand)
        ws.Names.Add Name:=sRngName, RefersTo:=rNotes
        Call Format_NoteRange(rNotes)
    End If
 
'---Check if "|Notes" Worksheet doesn't exist- add it
    If Not SheetExists(ws.Name & "|Notes") Then
        Set wsSave = ActiveSheet
        Sheets.Add
        ActiveSheet.Name = ws.Name & "|Notes"
        wsSave.Activate
    End If
 
'---Check if Notes DataTable doesn't exist- add it
    With Sheets(ws.Name & "|Notes")
        On Error Resume Next
        Set tblNotes = .ListObjects(1)
        If tblNotes Is Nothing Then
            .Cells(1) = "KeyPhrase"
            .Cells(1, 2) = "Ops Notes"
            .Cells(1, 3) = "PDR Notes"
            Set tblNotes = .ListObjects.Add(xlSrcRange, _
                .Range("A1:C2"), , xlYes)
        End If
    End With
 
'---Check if any PT fields are not Table Headers - add
    With tblNotes
        For Each ptField In PT.RowFields
            If IsError(Application.match(ptField.Name, .HeaderRowRange, 0)) Then
                .ListColumns.Add Position:=2
                .HeaderRowRange(1, 2) = ptField.Name
            End If
        Next ptField
    End With
    Check_Setup = True
    Exit Function
 
StopNotes:
    If NameExists(sRngName, ws.Name) Then
        Application.EnableEvents = False
        Call Clear_Notes_Range(ws)
        ws.Names(sRngName).Delete
        Application.EnableEvents = True
        Check_Setup = False
        Exit Function
    End If
End Function
 
Private Function Format_NoteRange(rNotes As Range)

'---Format body
    With rNotes
        .Interior.Color = 16316664
        .Font.Italic = False
        .HorizontalAlignment = xlLeft
        .IndentLevel = 1
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        .Borders(xlBottom).LineStyle = xlContinuous
        .Borders(xlRight).LineStyle = xlContinuous
        .Borders(xlLeft).LineStyle = xlContinuous
      .EntireColumn.AutoFit

       

    End With
 
'---Format optional header
    With rNotes.Resize(1).Offset(-1)
        .Cells(1).Value = "Calculation Details"
        .Cells(2).Value = "Back-Up Doc Links"
        .Interior.Color = 16316664
        .Font.Italic = False
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .Borders(xlBottom).LineStyle = xlContinuous
        .Borders(xlRight).LineStyle = xlContinuous
        .Borders(xlLeft).LineStyle = xlContinuous
        .Borders(xlTop).LineStyle = xlContinuous
          
    End With
End Function
 
Private Function Clear_Notes_Range(ws As Worksheet)
'---Clear existing notes range
    On Error Resume Next
    Dim c As Range
    With ws.Range(sRngName)
        With .Offset(-1).Resize(.Rows.Count + 1)
            If Intersect(ws.PivotTables(1).TableRange2, _
                    .Cells) Is Nothing Then
                .ClearContents
                .ClearFormats
            Else 'PT overlaps notes
                For Each c In .Cells
                    c.ClearContents
                    c.ClearFormats
                Next c
                On Error GoTo 0
            End If
        End With
    End With
End Function
 
Public Function Refresh_Notes(PT As PivotTable)
    Dim sField As String, sKey As String, sFormula As String
    Dim ptField As PivotField
    Dim tblNotes As ListObject
    Dim rNotes As Range, c As Range
    Dim rLabels As Range, rLabelsAll As Range
    Dim vFields As Variant, vReturn As Variant
    Dim lPosition As Long, lOffset As Long
    Dim i As Long, lIdx As Long
    Dim lRow As Long, lCol As Long
    
'---Clear existing notes range
    Call Clear_Notes_Range(ws:=PT.Parent)
'---Redefine and format notes range
    With PT.TableRange1
        Set rNotes = Intersect(PT.DataBodyRange.EntireRow, _
            .Resize(, 2).Offset(0, .Columns.Count))
    End With
    Set rNotes = rNotes.Resize(rNotes.Rows.Count + PT.ColumnGrand)
    PT.Parent.Names(sRngName).RefersTo = rNotes
    Call Format_NoteRange(rNotes)
 
'---Make array of rowfields by position to trace each row in hierarchy
     With PT.RowFields
        ReDim vFields(1 To .Count)
        For lIdx = 1 To .Count
            vFields(PT.RowFields(lIdx).Position) = PT.RowFields(lIdx).Name
        Next lIdx
    End With
 
'---Build formula to use as Match KeyPhrase
    Set tblNotes = Sheets(PT.Parent.Name & "|Notes").ListObjects(1)
    With tblNotes
        On Error Resume Next
        sFormula = "="
        For lIdx = LBound(vFields) To UBound(vFields)
            lCol = Application.match(vFields(lIdx), .HeaderRowRange, 0)
            sFormula = sFormula & "RC" & lCol & "&""|""&"
        Next lIdx
        sFormula = Left(sFormula, Len(sFormula) - 1)
        Intersect(.DataBodyRange, .ListColumns(1).Range).FormulaR1C1 = sFormula
    End With
 
'---Match KeyPhrases for each visible row of PT
    Application.EnableEvents = False
    lOffset = PT.TableRange1.Columns.Count + 1
    
    With PT.RowRange
        For lRow = 2 To .Rows.Count
            sKey = GetKey(rRowRange:=.Cells, _
                lNoteRow:=.Row + lRow - 1, vFields:=vFields)
            vReturn = Evaluate("=MATCH(""" & _
                sKey & """," & tblNotes.Name & "[KeyPhrase],0)")
            If (Not IsError(vReturn)) Then
                .Cells(lRow, lOffset) = Evaluate("=INDEX(" & tblNotes.Name & "[Ops Notes]," & vReturn & ")")
                .Cells(lRow, lOffset + 1) = Evaluate("=INDEX(" & tblNotes.Name & "[PDR Notes]," & vReturn & ")")
                
                Dim Hyperlink As Hyperlink
                
                If Evaluate("=INDEX(" & tblNotes.Name & "[Ops Notes]," & vReturn & ")").Hyperlinks.Count > 0 Then
                    Set Hyperlink = Evaluate("=INDEX(" & tblNotes.Name & "[Ops Notes]," & vReturn & ")").Hyperlinks(1)
                    If (Hyperlink.SubAddress = "") Then
                    .Cells(lRow, lOffset).Hyperlinks.Add .Cells(lRow, lOffset), Hyperlink.Address
                    Else
                    .Cells(lRow, lOffset).Hyperlinks.Add .Cells(lRow, lOffset), Hyperlink.Address, Hyperlink.SubAddress
                    End If
                End If
                
                If Evaluate("=INDEX(" & tblNotes.Name & "[PDR Notes]," & vReturn & ")").Hyperlinks.Count > 0 Then
                    Set Hyperlink = Evaluate("=INDEX(" & tblNotes.Name & "[PDR Notes]," & vReturn & ")").Hyperlinks(1)
                    If (Hyperlink.SubAddress = "") Then
                    .Cells(lRow, lOffset + 1).Hyperlinks.Add .Cells(lRow, lOffset + 1), Hyperlink.Address
                    Else
                    .Cells(lRow, lOffset + 1).Hyperlinks.Add .Cells(lRow, lOffset + 1), Hyperlink.Address, Hyperlink.SubAddress
                    End If
                End If
                
            End If
        Next lRow
    End With
    Application.EnableEvents = True
End Function

Private Function GetKey(rRowRange As Range, lNoteRow As Long, _
        vFields As Variant) As String
        
    Dim sFieldCurr As String, sFieldPrev As String, sNew As String, sField As String
    Dim rLabels As Range
    Dim lIdx As Long, i As Long, lPosition As Long, lCol As Long
    With rRowRange '--Compact layout
        If .PivotTable.RowFields(1).LayoutCompactRow Then
            lIdx = UBound(vFields) + 1
            Set rLabels = .Offset(1).Resize(lNoteRow - .Row)
            For i = rLabels.Rows.Count To 1 Step -1
                sField = rLabels(i).PivotField.Name
                lPosition = Application.match(sField, vFields, 0)
                Do While lIdx > lPosition + 1
                    GetKey = "|" & GetKey
                    lIdx = lIdx - 1
                Loop
                If lPosition < lIdx Then
                    GetKey = rLabels(i).PivotItem.Name & "|" & GetKey
                    lIdx = lPosition
                    If lIdx = 1 Then Exit For
                End If
            Next i
        Else    '--Tabular or Outline layout
            For lCol = 1 To .Columns.Count
                With .Cells(lNoteRow - .Row + 1, lCol)
                    sFieldCurr = .PivotField.Name
                    sNew = IIf(sFieldCurr = sFieldPrev, "", .PivotItem.Name)
                    GetKey = GetKey & sNew & "|"
                    sFieldPrev = sFieldCurr
                End With
            Next lCol
        End If
    End With
 End Function
 
Public Function Update_Note_Database(PT As PivotTable, rNote As Range)
    Dim rLabels As Range
    Dim sField As String, sItem As String
    Dim vFields As Variant, tblNotes As ListObject
    Dim lPosition As Long, lIdx As Long, lCol As Long
    Dim iArray As Variant, i As Integer
    Dim Hyperlink As Hyperlink
 
    
    Set tblNotes = Sheets(PT.Parent.Name & "|Notes").ListObjects(1)
    tblNotes.ListRows.Add (1)
    
    tblNotes.ListColumns("Ops Notes").Range(2) = rNote(1).Value
    tblNotes.ListColumns("PDR Notes").Range(2) = rNote(1, 2).Value
    
    If rNote(1).Hyperlinks.Count > 0 Then
        Set Hyperlink = rNote(1).Hyperlinks(1)
        Dim range1
        Set range1 = tblNotes.ListColumns("Ops Notes").Range(2)
        If (Hyperlink.SubAddress = "") Then
            tblNotes.ListColumns("Ops Notes").Range(2).Hyperlinks.Add range1, Hyperlink.Address
        Else
            tblNotes.ListColumns("Ops Notes").Range(2).Hyperlinks.Add range1, Hyperlink.Address, Hyperlink.SubAddress
        End If
       
    End If
    If rNote(1, 2).Hyperlinks.Count > 0 Then
        Set Hyperlink = rNote(1, 2).Hyperlinks(1)
        Dim range2
        Set range2 = tblNotes.ListColumns("PDR Notes").Range(2)
        If (Hyperlink.SubAddress = "") Then
            tblNotes.ListColumns("PDR Notes").Range(2).Hyperlinks.Add range2, Hyperlink.Address
        Else
            tblNotes.ListColumns("PDR Notes").Range(2).Hyperlinks.Add range2, Hyperlink.Address, Hyperlink.SubAddress
        End If
        
    End If
    
    If PT.RowFields(1).LayoutCompactRow Then '--Compact layout
    '---Make array of rowfields by position to trace each row in hierarchy
         With PT.RowFields
            ReDim vFields(1 To .Count)
            For lIdx = 1 To .Count
                vFields(PT.RowFields(lIdx).Position) = PT.RowFields(lIdx).Name
            Next lIdx
        End With
     
    '---Make new record of note at top of database table
        lIdx = lIdx + 1
        With PT.RowRange
            Set rLabels = .Offset(1).Resize(rNote.Row - .Row)
            For i = rLabels.Rows.Count To 1 Step -1
                sField = rLabels(i).PivotField.Name
                lPosition = Application.match(sField, vFields, 0)
                If lPosition < lIdx Then
                    sItem = rLabels(i).PivotItem.Name
                    tblNotes.ListColumns(sField).Range(2) = sItem
                    lIdx = lPosition
                    If lIdx = 1 Then Exit For
                End If
            Next i
        End With
    Else    '--Tabular or Outline layout
        Set rLabels = Intersect(rNote.EntireRow, PT.RowRange)
       
        For lCol = rLabels.Columns.Count To 1 Step -1
            With rLabels(1, lCol)
                tblNotes.ListColumns(.PivotField.Name).Range(2) = .PivotItem.Name
            End With
        Next lCol
    End If
    
'---Remove any previous notes with matching rowfield values
    With tblNotes.Range
        ReDim iArray(0 To .Columns.Count - 4)
        For i = 0 To UBound(iArray)
            iArray(i) = i + 2
        Next i
        .RemoveDuplicates Columns:=(iArray), Header:=xlYes
        If rNote(1).Value = "" And rNote(1, 2).Value = "" Then _
            tblNotes.ListRows(1).Delete
    End With
End Function
 
Private Function NameExists(sRngName As String, _
        sSheetName As String) As Boolean
    Dim rTest As Range
    On Error Resume Next
    Set rTest = Sheets(sSheetName).Range(sRngName)
    NameExists = Not rTest Is Nothing
End Function
 
Private Function SheetExists(sSheetName As String) As Boolean
    Dim sTest As String
    On Error Resume Next
    sTest = Worksheets(sSheetName).Name
    SheetExists = LCase(sTest) = LCase(sSheetName)
End Function
 
Upvote 0
Hello, just had to sign up for MrExcel so I could thank Jerry for this extremely useful code. For some reason, though, I'm getting a glitch. I am an absolute beginner with VBA, but when I first enter a note I get a a "compile error: method or data member not found." It seems to be highlighting "With rPC.PivotCell.RowItems" as being the problem. I tried this on a couple of different worksheets but it kept coming up. Does anyone have any suggestions about what could be the issue? I can try to provide more info.
 
Upvote 0
Hi J-H-H,

You might have some scenario I hadn't anticipated (perhaps a PivotTable that has no RowItems?).

To narrow things down a bit, when the code generates the error, go to the Debugger, (without stopping the code), then in the Immediate Window enter:

?rPC.Address

If a range address is returned to the next line, look at your sheet and post what part of the PivotTable that range references.
 
Upvote 0
Thanks very much for your quick reply, Jerry. I did try the debugger and ended up with a Runtime error 424: Object needed -- though actually this isn't my biggest priority at the moment. I've had a little more success with the earlier version of the code (the first one posted in this thread) but have experienced another problem that I can't figure out.

By the way, I'm using Excel 2011 on a Mac.

The problem I'm encountering with the earlier version of the code is this, and I'd appreciate it if anyone has any suggestions: the notes column is successfully created and I can enter notes with no problem, but when I expand and collapse fields in the PT, the notes aren't moving.

When I go over and look at the Notes worksheet that is created, I see that the only fields that are populated are what I have entered in 'notes' column, as well as the column which represents the first thing I have in my Row Labels column in the PT (I have five things all together that are Row Labels, but there is nothing in any of those columns on the Notes sheet.) In addition, there's nothing in the KeyPhrase column.

(Sorry about my use of the word "things" here -- I don't want to use the wrong terminology since I don't think I know it. I'm afraid my lack of facility with Excel jargon may be hampering my ability to explain myself!)

I felt like there must be something slightly out of the ordinary with my data, but I also tried the code with some dummy data and I seem to have the same problem.

I'd be happy to try to provide any other information that would give some more context.

thanks!

Joel
 
Upvote 0
Joel, I'd rather try to help get the current version of the code working for you than to go back to troubleshoot one of the earlier versions.

Could you mockup a small example file that errors as you describe in your first post?
You can upload that to a hosting site like Box.com or send me a PM and we'll exchange email addresses.
 
Upvote 0
Joel, I'd rather try to help get the current version of the code working for you than to go back to troubleshoot one of the earlier versions.

Could you mockup a small example file that errors as you describe in your first post?
You can upload that to a hosting site like Box.com or send me a PM and we'll exchange email addresses.


Will send a PM - thanks.
 
Upvote 0
JS411 - I know it's been a while since you created this code, but I'm hoping you can help me out. I used the code you created for 2 columns of notes and attempted to adapt if for 4 columns of notes. It seems to be working correctly, except the 'keyphrase' formula is not pulling through to the 'PIVOT-Notes' tab. Could you please let me know if you have any thoughts as to why this would be happening (or post code that is already adapted for 4 columns of notes). I'd be happy to send you my edited code if that helps at all.


Thanks in advance for any help you might be able to provide! This is a great piece of code!!
 
Upvote 0

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top