If ws.PivotTables.Count 1 Then GoTo StopNotes '-Check if not exactly one PT on Worksheet- exit Public Function Check_Setup(ws As Worksheet) As Booleanĭim PT As PivotTable, ptField As PivotField
#Quick notes on how to use pivot tables in excel code#
To try it out, paste this code into a Standard Code Module There's so many options and scenarios for PivotTables, that I expect you'll find some glitches through your testing. Whenever the PivotTable is updated (expanded, collapsed, sorted, refreshed), the notes are cleared and then rewritten to correct positions for the revised PivotTable display.īecause this is fairly complex, I'd encourage you to extensively test this prior to using it for a real world application (and even then make sure to have a back up of your file).Īt this point, I'm hopeful this will work for the basic parameters you described. this added sheet can be hidden if you prefer. The code allows the user to write notes in the first column of cells to the right of the PivotTable.Īs notes are added, revised or deleted, Worksheet_Change event code maintains a data table of the notes on an added sheet.Įach note is indexed by its associated RowField PivotItems. Well, this turned out to be harder than I anticipated, but I finally have some code for you to try. What would you want to happen to the comments when the data they reference is no longer displayed? With a PivotTable, the report can change to show entirely different summaries and views of the source data (changing ReportPages, Filtering, Changing Position of RowField Labels, Grouping, Adding/Removing Fields). That would be somewhat complex and only solve part of the problem. VBA could probably be used to enable notes to "move" with the data or labels they reference, using a syntax like GETPIVOTDATA to key on a combination of field references. That being said, this isn't very useful because the Comments stay with those Cells when the data is reorganized or filtered, so it has the same limitations of placing notes in the Column outside the PivotTable.
"Add Comment" does not appear in the standard Right-Click menu for PivotTable Cells however you can go to the Review Tab of the Ribbon and pick "Add Comment". Due to the dynamic nature of the PivotTable, I don't think there is a simple solution to do what you describe.Įxcel does allow you to add comments to the Cells of the PivotTables.