Pivot Notes documentation
5 Steps to start working with Pivot Notes
Pivot Notes was written by Mark Kubiszyn
code attributionin situ
' © Copyright/Author: ' Mark Kubiszyn 2012-2017. All Rights Reserved ' Website/Follow: ' http://www.kubiszyn.co.uk/ ' https://www.facebook.com/Kubiszyn.co.uk/
You can Purchase Pivot Notes for £2.99 via FastSpring by clicking here
Here I will describe how to get the correct Pivot Table Field Name to use as the Index when congiuring Pivot Notes - failure to identify the correct Field Names will cause errors when attempting to Index a Pivot Table
If someone has changed the Field Name:
If someone has changed the Field Name you can check this by clicking anywhere in the Pivot Table to view the Field List on the right-hand side. If this does not automatically pop up, then you can bring the Field List up by selecting 'Field List' on the 'Show' Group of the 'PIVOTTABLE TOOLS', 'ANALYZE' Tab of the Ribbon. Now check the Field Name in the respective area ie. 'FILTERS', 'COLUMNS' or 'ROWS'. Typically the 'VALUES' area will not be used for Indexed Fields when using Pivot Notes. The Name that you see for the Field will represent the correct Name displayed in the Pivot Table - try it, change one of your Field Names and you will see that the Name changes in the Field List too
If you are using the Data Model:
In order to ensure that you are Indexing the correct Pivot Table Fields when using the Data Model, you can use the following Code to determine the Field Names and then enter the Name into the Pivot Note Field Names Setting ie. "[Region_Data].[Region].[Region]". Press ALT+F8 to enter the VBA Editor and in the Code Module where you pasted Pivot Notes Code add the Subroutine. Click into the Subroutine and press F5. A Message Box will pop up with the Names. They will also be printed to the Immediate Debug Window so you can simply Copy & Paste the Names into the Settings Variable in the Setup section
Sub ListPivotTableFieldNames() Dim objPivotTable As PivotTable Dim objPivotField As PivotField For Each objPivotTable In ActiveSheet.PivotTables For Each objPivotField In objPivotTable.PivotFields MsgBox "Data Model Field to use in Pivot Notes Settings: " & objPivotField & vbLf & vbLf & "Original Field Name without Data Model: " & objPivotField.Caption Debug.Print "Data Model Field to use in Pivot Notes Settings: " & objPivotField Debug.Print "Original Field Name without Data Model: " & objPivotField.Caption Next objPivotField Next objPivotTable End Sub
Here are the results of a Pivot Table that uses the Data Model. The different Names have been highlighted - these are the Names that you need to use when configuring Pivot Notes prior to Indexing:
Data Model Field to use in Pivot Notes Settings: [Region_Data].[Region].[Region] Original Field Name without Data Model: Region Data Model Field to use in Pivot Notes Settings: [Product_Data].[Prod Cat].[Prod Cat] Original Field Name without Data Model: Prod Cat Data Model Field to use in Pivot Notes Settings: [Measures].[Sum of Revenue] Original Field Name without Data Model: Sum of Revenue
Choosing how to Index the Pivot Table is very important. You need to remember that in order for Pivot Notes to store and sort Notes correctly it must have a unique Index creating from one or more visible Pivot Table Fileds. If you don't get this bit right, Notes will not be rendered correctly and may appear to move around or not be saved at all. Here are some key points:
1. If possible, choose 1 visible Field that is unique for every single Row in the Pivot Table and Index by that Field
2. If step 1. is not possible then you need to Index by more than one Field in order to create a unique Index. Remember that these must be visible in the Pivot Table so Repeat Label Items should be switched on. If you don't want to repeat Item Labels you can hide the Fields using Conditional Formatting afterwards. Sometimes you may have to Index by 3 or 4 Fields in order to have a truly unique Index
Pivot Table Department Vendor A Department A Vendor B Department A Vendor C Department A Vendor Index A Department/A Vendor/ B Department/A Vendor/ C Department/A Vendor/
Pivot Table Department Vendor A Department A Vendor B Department A Vendor C Department A Vendor C Department A Vendor Index A Department/A Vendor/ B Department/A Vendor/ C Department/A Vendor/
Please Note: if your Pivot Table has Blanks in the Fields you should 'Exclude' these prior to Indexing the Pivot Table
' ## the Name of your Pivot Table. click on the Pivot Table and check the setting for the "PivotTable" Group on the "ANALYZE" Tab of the Ribbon ' - a check will be made by the Code when Indexing this Pivot Table Private Const PivotTableNamePT1 As String = "PivotTable1" ' ## the Name of the Worksheet where your Pivot Table is stored. we will not use ActiveSheet! ' we need to explicitly refer to this in case of Sheet switching whilst in Cell Edit Mode (F2) and also for error reporting ' - this prevents the potential loss of Notes if a user decides to go to another Worksheet whilst editing a Note ' - a check will be made by the Code when Indexing a Pivot Table for this Worksheet Private Const PivotTableWorksheetNamePT1 As String = "Example" ' ## the Name of the Worksheet where your Pivot Notes will be stored ' remember you can hide this Worksheet (xlSheetHidden or xlSheetVeryHidden), the Code will still work ' - you can store Notes for more than 1 Pivot Table in a single Worksheet ' - Pivot Notes Styles are also stored in the "Notes" Worksheet. you can Copy this directly into a new Project Private Const NotesWorksheetNamePT1 As String = "Notes" ' ## the Index start Cell for your stored Pivot Notes ' typically this will be the first Cell where you have added the Headers ie. Index, Notes 1, Notes 2, Notes 3 ' the Notes are stored in multiple Columns per Pivot Table in the Notes Worksheet specified above ' - a gap of at least 1 Column should be left between Notes when using more than 1 Pivot Table Private Const NotesIndexCellPT1 As String = "A1" ' ## the Field Names that will be used as the unique Index for the Pivot Table to link your Pivot Notes ' ensure that you separate these using a colon ':' if you enter more than one Field Name ie. "Field 1:Field 2" etc. ' remember to pass these in the order that you want the Index to be created, preferably left to right ' - there is no longer any requirement to specify an offset, the first Field is where the Code will begin to create an Index Private Const PivotTableFieldNamesPT1 As String = "Material:Key Figure" ' ## the delimiter that is used when Indexing the Pivot Table ie. "-", ", ", "*", " / ", "~" etc. ' remember when using Date Fields it may be wise to use a tilda "~" ie. ~03/09/2016~ will allow the Code to split & Index correctly Private Const IndexDelimiterPT1 As String = "~" ' ## the Style(s) that you want to give your Pivot Note Headers - the Headers for the Notes displayed alongside your Pivot Table ' - Styles are stored in the "Notes" Worksheet ' - you can now pass in different Styles for each Pivot Notes Header Column that you are using ' - these can be passed in using a colon-delimited list ie. "Pivot Notes head6:Pivot Notes head5:Pivot Notes head7" ' - if you don't pass enough Styles for all of your Header Note Columns the code will use the first Style as the default ' - when changing this setting retrospectively, you may need to Index the Table again to pull in the New Style Private Const PivotNotesHeaderStylePT1 As String = "Pivot Notes head6:Pivot Notes head5:Pivot Notes head7" ' ## the Style(s) that you want to give your Pivot Notes - the Notes displayed alongside your Pivot Table ' - modify or create your own Styles and then pass the Style Name into the PivotNotesStyle setting ' - Styles are stored in the "Notes" Worksheet. you should always Copy this into a new Project ' - you can now pass in different Styles for each Pivot Notes Column that you are using ' - these can be passed in using a colon-delimited list ie. "Pivot Notes text6:Pivot Notes text5:Pivot Notes text7" ' - if you don't pass enough Styles for all of your Note Columns the code will use the first Style as the default ' - when changing this setting retrospectively, you may need to Index the Table again to pull in the New Style Private Const PivotNotesStylePT1 As String = "Pivot Notes text6:Pivot Notes text5:Pivot Notes text7"
You can change how you would like to Index your Pivot Tables and store Notes by a Variable in the Setup Section called IndexBehaviour. Here is the Variable with its default setting (you change the setting after the '.' full-stop or period symbol):
Private Const IndexBehaviour = IndexBehaviourType.xlRetainOldIndexesAndAppendNewSourceIndexes
The 3 Options available are as follows every time that you Index a Pivot Table:
IndexBehaviourType.xlRemoveAllIndexesall Indexes & Notes will be removed - allows you to start keeping Notes again from scratch by deleting all of the Indexes and Notes
IndexBehaviourType.xlRetainOldIndexesAndAppendNewSourceIndexesexisting Indexes will be retained and any new Indexes appended - DEFAULT: allows you to keep Notes forever, appending Notes regardless of whether they belong to the previous or current Pivot Table Source data
IndexBehaviourType.xlRetainSourceIndexesOnlyonly Indexes matching the Pivot Table Source data will be retained - allows you to only keep Notes that only belong to the current Pivot Table Source data - all older Notes will be deleted
Pivot Notes uses Styles for formatting Notes and is designed to allow 1 Colour per Tag per Column, but what if you want to highlight 2 or more different Tags? Well this is possible, but you need to make a couple of changes to the settings and add a snippet of Code. Here I will show you how to change the Code to have a standard light Grey Tag '>>', a Green Tag '#' and a Red Tag '!'. Please Note: these changes will apply across all Columns of Pivot Notes - you cannot simply just change the Cell Colour or formatting because the Styles are overwritten by the RenderNotes() Subroutine
Using the Example Code in Pivot Notes Version 5, you need to adjust the HighlightColour in the Setup Section to explicitly set the different Colours per Column for each Tag:
Private Const ConditionalFormatTag As String = ">>:>>:>>" Private Const HighlightColour As String = rgbGreyHighlight & ":" & rgbGreyHighlight & ":" & rgbGreyHighlight
rngFormatAddress.FormatConditions.Add Type:=xlTextString, String:="#", TextOperator:=xlContains With rngFormatAddress.FormatConditions(2) ' // only use if the Format requires Priority over all other existing Formats '.SetFirstPriority ' // each global highlight colours .Interior.Color = vbGreen ' // the default Font Colour .Font.Color = vbWhite ' // only set to True if you wish to stop after this Format. we will apply the default .StopIfTrue = False End With rngFormatAddress.FormatConditions.Add Type:=xlTextString, String:="!", TextOperator:=xlContains With rngFormatAddress.FormatConditions(3) ' // only use if the Format requires Priority over all other existing Formats '.SetFirstPriority ' // each global highlight colours .Interior.Color = vbRed ' // the default Font Colour .Font.Color = vbWhite ' // only set to True if you wish to stop after this Format. we will apply the default .StopIfTrue = False End With
You can move a Pivot Table that is using Pivot Notes anywhere in the Worksheet without changing any of the settings for Pivot Notes. To do this, click on any Cell within the Pivot Table and select the 'ANALYSE' Tab of the Ribbon. Click the 'Move PivotTable Button' in the 'Actions' Group. Select another Cell in the existing Worksheet and press 'OK'
The Pivot Table will move to the new Cell leaving the Notes on the Right-hand side of the Worksheet. Now right-click->Refresh anywhere on the Pivot Table. The Pivot Notes will be rendered next to your Pivot Table
Please Note: Pivot Notes may not clean up areas that overlap depending on where you Move the Table to (it may be that you are moving the Table somewhere within the Notes Range themselves) - if you see any old Notes, simply select them and clear the Formats. Refresh the Pivot Table again
Styles are at the heart of Pivot Notes for rendering Note Headers and Text. You can use my Pivot Notes Style Sheet to Copy Styles into your own Workbooks. This is particularly useful when setting up Pivot Notes from scratch for a new Pivot Table - it means that you don't have to Copy the entire 'Notes' Worksheet from PivotNotes.xlsm, you can just Copy the Styles that you need
Style Sheet.xlsx (11.7 KB)
When configuring Pivot Notes or if you are using Pivot Notes and wish to completely redesign your Pivot Table, Pivot Notes will send out lots of errors because it is constantly firing Code, trapping the Worksheet_PivotTableChangeSync() Event Handler. You can switch off Pivot Notes until you are ready to use it by adjusting the 'DebugMode' Setting in the Global Settings section of Setup in the Code to 'True'. This means that everytime an Event Handler is fired the Code will exit without throwing any error messages. The Setting is shown below:
Private Const DebugMode As Boolean = False ' set this to True when redesigning Pivot Tables using Pivot Notes
Q. Can I use Find & Replace with Pivot Notes?
A. Yes, you can do a Find & Replace to find and replace text within your Pivot Notes as you would normally with a Worksheet. When you do this, the replaced Notes are automatically updated
Q. Can I have 10 or 20 or 50 Columns of Notes?
A. There is no set limit. I have tested with 100 Columns of Notes for a small Pivot Table with no loss of speed. You can have multiple Columns of Notes that use different Styles for both the Headers and Note Columns
Q. Can I supress the Warning Message informing me that: 'There's already data in [Pivot Notes.xlsm]. Do you want to replace it?'
A. No, unfortunately this message is processed before any Code can manipulate it. It is the Pivot Table determining that it needs to resize itself to allow the correct amount of data to be displayed whenever Text is present next to a Pivot Table Field - simply accept the Message by clicking 'OK' and the Table will resize and your Pivot Notes will be rendered next to the newly resized Table
Q. Can I highlight Notes different Colours for specific Tags in all Columns?
A. Yes, you can only use 1 Tag and 1 Colour per Column though - simply pass in the relevant number of Tags and Colours, one of each for every Note Column in the Global settings for 'ConditionalFormatTag' and 'HighlightColour', like this:
Private Const ConditionalFormatTag As String = ">>:feedback:!" Private Const HighlightColour As String = "14479865:16183786:15921906"
Public Function AddValues(cell1 As Long, cell2 As Long) As Long AddValues = cell1 + cell2 End Function
' ## the Style that you want to give your Pivot Notes - the Notes alongside your Pivot Table Private Const PivotNotesStylePT1 As String = "Pivot Notes text6:Pivot Notes text5:Pivot Notes text7" ' ## the Style that you want to give your Pivot Notes Headers - the Headers for the Notes displayed alongside your Pivot Table Private Const PivotNotesHeaderStylePT1 As String = "Pivot Notes head6:Pivot Notes head5:Pivot Notes head7"
Private Const IndexDelimiterPT1 As String = "~"
Pivot Notes showing a single Product Filtered by the 'Material' Pivot Table Field and Collapsed with a Note left underneath the 'Customer Information' Pivot Note Header - this is an aggregated Note showing you what you can do if you want to store Notes at different levels, of course you may only want to store Notes at a single level (this Pivot Table has been Indexed by the 'Material' and 'Key Figure' Pivot Table Fields):
Pivot Notes showing a single Product Filtered by 'Material' and Expanded with Notes left next to the 'Total Sales', 'Key Figure' Pivot Table Field underneath the 'Customer Information' and 'Commercial Team' Pivot Note Headers:
Pivot Notes Unfiltered and showing additional Calendar Weeks of data picked by the Slicer Control. In this Screen Shot the Demand Team have left a Note against Product 17468 underneath the 'Demand Team' Pivot Note Header indicating that there are no sales for the Product against the 'Final Forecast' Pivot Table Field:
Support is only given to Purchased Software. You should only contact me on the Support Email address supplied by FastSpring - please provide proof of purchase ie. Email address or Order Reference
25.05.2017 - (Version 5)
a) rebuild the Pivot Notes Workbook to be 1 Example Pivot Table Worksheet, 1 Notes Worksheet containing Styles and 1 Data Worksheet this is an effort to makes things more straightforward and ease future issues as I will only be updating 1 Source File
b) go through the Code and Comments tidying up anything that does not make sense or should not be there!
c) add the ability to use Hyperlinks. this is done via a small routine inserted into the RenderNotes() Subroutine. a user may also use a Hyperlink as the Style so that the Cell resembles a Hyperlink - drawback will be a degradation in speed for large Pivot Tables
08.02.2017 - (Version 4.2 & Version 4.2.1 - bug fix)
a) Change to allow Pivot Notes to use a different Style for each Note Column and/or Note Header if required. This is useful not just for visual appearances, but also for Custom formats for Dates, Times etc. Note and Header Style Names are passed using colon-delimited lists
b) Code to add/remove Date/Time Stamps en masse to a separate Column and to lock a specific Column has been added to the Worksheet_Change() Event Handler - this is commented out and can be used by uncommenting the relevant code part(s) as required
c) Add in the ability to fix the Header Row Height. this is useful to emphasise the Headers by making them taller
d) Add in the ability to use Data Validation through the use of a Global Variable. Data Validation should be added to any of the Headers in the "Notes" Worksheet - this will then be transposed down the respective Column next to the Pivot Table. Data Validation can be any type of validation and can include Formula lists from another part of the Workbook Other features of Data Validation can be used ie. when you click into a Cell in the Column a Message can be displayed to the user or you could restrict data input
e) Add in a Global Enumeration Variable to control the Indexing behaviour whenever the Pivot Table Source data requires refreshing:
i) xlRemoveAllIndexes = all Indexes & Notes will be removed
(allows you to start keeping Notes again from scratch)
ii) xlRetainOldIndexesAndAppendNewSourceIndexes = existing Indexes will be retained and any new Indexes appended
(DEFAULT: allows you to keep Notes forever, regardless of whether they belong to the previous or current Pivot Table Source data)
iii) xlRetainSourceIndexesOnly = only Indexes matching the Pivot Table Source data will be retained
(allows you to only keep Notes that only belong to the current Pivot Table Source data)
f) Speed increase for larger tables when updating Notes. Now only Dictionary Indexes for the Target area of Notes are built which are queried against a Notes Dictionary of Indexes - if they exist then only the Taret area of Notes are updated