Version 5 (Please Note: this is not the latest version of this software)
If you have already purchased this Software, let me take a moment to thank you for being a loyal customer
You are entitled to free lifetime updates for ALL future builds
This documentation is to help you understand the Software and to give you a more meaningful insight into what it can do. Please go through the documentation and read it carefully · Basic to Moderate VBA & Excel skills are required
You will need the following Excel Version & Prerequisite to use this Software
No support is provided for customization or development of this Software
Pivot Notes allows you to store multiple Columns of Notes and Formula alongside one or more Tabular or Outline Pivot Tables and use the +/- Buttons to Expand or Collapse separate hierarchy's of Note Columns. You can Filter or Sort Pivot Items and the Notes will persist. If the Pivot Table Source data changes, you can Index the Table to store new Notes whilst retaining any existing Notes. Please Note: this is not the latest version of this software. Version 8 is now available - please visit: Pivot Notes 8
PIVOT NOTES VERSION 5 HAS NOW BEEN DEPRECATED (25/10/2020)
1. Copy all of the Code from the 'Example' Worksheet Code Module in the 'PivotNotes.xlsm' File into your own Worksheet Code Module
2. Copy the 'Notes' Worksheet which includes the Styles into your own Workbook; alternatively, you can simply rename an exisiting Worksheet to 'Notes' and just Copy specific Styles from the 'PivotNotes.xlsm' File or Copy Styles from my Pivot Notes Style Sheet. You can also use any of the existing Excel Styles
3. Edit the Setup Section at the top of the Code Module - press ALT+F11 to open the Editor, double-click 'Sheet1(Example)' & scroll down. Adjust the variables for 'PivotTableNamePT1', 'PivotTableWorksheetNamePT1', 'NotesWorksheetNamePT1' etc. (whilst you are setting up Pivot Notes, you can set the 'DebugMode' to True so that the Pivot Notes Code does not fire for any Table actions)
4. Index the Table by pressing ALT+F8 and run the IndexTable Subroutine
5. Filter, Slice or right-click to Refresh the Pivot Table
Field Names need to be added to the Code settings as a colon delimited String for Indexing the Pivot Table. 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 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
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. Here is the Code to establish Data Model Field Names:
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
add these names to the Code:
- normal Private Const PivotTableFieldNamesPT1 As String = "Region:Prod Cat:Sum of Revenue" - data model Private Const PivotTableFieldNamesPT1 As String = "[Region_Data].[Region].[Region]:[Product_Data].[Prod Cat].[Prod Cat]:[Measures].[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
The following is a unique Index for 2 visible Fields, 'Department' and 'Vendor':
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/
The following is not a unique Index for the same Fields and will not work as Notes will only ever be left at the first occurance of the repeated Index 'C Department/A Vendor/' and actually due to the way that Pivot Notes renders Notes for multiple heirarchy's, Notes may also appear to move around. In this case another Field is required to Index the Pivot Table correctly:
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
A Setup Section is provided at the top of the Pivot Notes Code Module that allows you to change any of the local settings for your Pivot Table, Pivot Table Names, Worksheet Names, Pivot Table Field Names, Pivot Notes Styles etc. Here are all of the Pivot Note Local Settings - these apply for each Pivot Table in a Worksheet using Pivot Notes (typically, you will need to edit all of these apart from the IndexDelimiterPT1 which can be left as a Tilde symbol "~"):
' ## 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"
The use of the "PT1" suffix allows you to create new sets of editable Variables when using multiple Pivot Tables on a Worksheet. These settings can be replicated and will be used by Different Pivot Tables using Pivot Notes within this Worksheet - if you are only using a single Pivot Table then you don't have to worry about this
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
Now you need to add a bit of Code for each separate Conditional Format you require underneath the first Format (after the 'Else' switch) in the RenderNotes() Subroutine:
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
Now you should be able to Highlight using different Colours for different Tags in Columns:
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 needStyle 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
Pivot Notes can store and sort Formula for Pivot Table Fields and for Pivot Table data using normal Formula or the =GETPIVOTDATA() Function - just as it would if you were writing it in a Column next to a Pivot Table that was not using Pivot Notes. The main difference using Pivot Notes to store Formula is that when you refresh the Source data for a Pivot Table the Formula will persist next to the Pivot Table Row where the Note was stored. The only thing you need to be careful about is not to cause any stored Notes circular references. Let's add a simple Formula to reference the Customer Cell in the Pivot Table, remembering to lock the Column:
Now we will Slice the Table for 2 Weeks and for MINERALS - the Formula still references the correct Customer, "Customer A"
This example luckily did not result in a stored Note circular reference. So what about something a little more complex and what about the circular references. Consider a Formula that counts the number of gaps for any number of Weeks Sales per Customer and idetifies whether they have traded or have been trading intermittently. I will enter the Formula below, locking the relevant Columns and copy it down the Range. Then I will remove the Formula for Sub Totals and Grand Totals. The Formula can be seen below (notice I have changed my Pivot Note Columns using a simple refresh)
If I select Week 38.2016 to Week 42.2016 for just 'Customer E' I now have a view of how each Customer is trading up until Week 42.2016 (there is no data after Week 42 yet in my Source data). You can see 'Customer E' is trading intermittently on 'Product B' and not trading at all on 'Product D'
I can now carry on leaving Notes - the Formula will persist. So, what about the circular references? Excel hasn't reported anthing unusual. Well if I click into the "Notes" Worksheet and double-click a Formula Cell, you can see that indeed the Formula does reference itself. Closing and re-opening the Workbook will cause a circular Formula warning which we don't want
So, how can we fix this. Well the answer is to count how many Columns the Pivot Table uses plus Column A and move our Notes at least that distance away in the "Notes" Worksheet. To start, ensure that you have no Filters on the Pivot Table. Go to the "Notes" Worksheet. Insert a Column and then press "F4" to repeat for as many Columns as you need (I did 18). Press ALT+F8 to enter the VBA Editor and change the Pivot Notes Index Cell to the start of your moved Notes ie. I changed mine to "S1". Now, the Formulas in the "Notes" Worksheet will have changed, but we have a neat trick to update them again. Go back to the Notes alongside your Pivot Table, press "F2" in one of the Notes and press "Enter". All of the Notes will update and we have fixed the circular Formula issue (remember as long as the Notes Formula do not refer to themselves the fact that they are calculating something different on the "Notes" Worksheet is irrelevant)
When adding Data Validation as a List linking to a Range, unless you explicitly link the Range to the same Worksheet as your Pivot Table, then you MUST add the Range specifying the Name of the Worksheet like this:
=Sheet2!A1:A4 ' Excel may remove the Name of the Worksheet when it displays the Address, but it will be recognised correctly as a Range
Data validation is a feature available in Microsoft Excel. It allows you to do the following:
- Make a list of the entries that restricts the values allowed in a cell
- Create a prompt message explaining the kind of data allowed in a cell
- Create messages that appear when incorrect data has been entered
You can add Data Validation for Pivot Note Columns by adding them to one or more of your Note Headers in the "Notes" Worksheet. Here I will add a simple Drop-down list to pick a,b,c in a Cell by adding it to the "commercial" Note Header in my "Notes" Worksheet
Select the Notes Header "Commercial" in my "Notes" Worksheet
On the Data menu, click Validation
On the Settings tab, click List in the Allow drop-down list
By default, the Ignore blank and In-cell Dropdown check boxes are selected
In the Source box, type a,b,c
Now you need to go into the Code and set the UseDataValidation Variable to True
Private Const UseDataValidation As Boolean = True 'False
And here is the result of my Data Validation following a right-click, refresh of my Pivot Table allowing me to use a Drop-down Combo Box within my Pivot Notes:
If you want to change the Background Colour of a Worksheet you may find that using Pivot Tables remove the Colour when they resize leaving the default 'No Colour' displaying Gridlines. A good way around this is to use a Background Image
You just need to create a small '.JPG' image about 16 pixels by 16 pixels. I have one here if you want to download and use this one (open the image and use right-click to download or Save as...)
Then simply select the Worksheet that your Pivot Table is on and on the 'Page Setup' Group of the 'PAGE LAYOUT' Tab of the Ribbon click the 'Background' Button. Browse to where you saved the image and select it. The Worksheet will now change Colour to the same Colour as your image. If you have any Styles, these will always over-ride the image and in fact you can still use any kind of Fill to change the Colour of a Cell even with the Background using an image. Now try Slicing or resizing your Pivot Table - it will now have the image as it's background too, neat eh?
A great Feature with Pivot Notes is the ability to use multiple Styles for both the Note Headers and the Note Columns. To do this you simply create the Styles that you want to use for both the Headers and Note Columns and then pass the Names of the Styles as Colon-delimited lists for the Variables below:
Private Const PivotNotesHeaderStylePT1 As String = "Pivot Notes head1:Pivot Notes head2:Pivot Notes head3" Private Const PivotNotesStylePT1 As String = "Pivot Notes col1:Pivot Notes col2:Pivot Notes col3"
If you don't pass enough Styles for all of your Headers or Note Columns the code will use the first Style as the default for all of the Headers or Note Columns
To create a new Style, first Duplicate a Style by selecting a Blank Cell and then on the 'Styles' Group of the 'HOME' Tab of the Ribbon right-click any Style and select 'Duplicate...'. A Style Dialog will popup and you can set the Parameters for your new Style including a New Name for the Style - the New Name is the Name that you should add to the Variables above. Take a look at the "4.2" Worksheet and the "Notes" Worksheet as I have used the Styles created on the "Notes" Worksheet for the Pivot Note Headers and Note Columns
Remember you can have different Custom Formats for your Styles too, like Dates, Times etc.
You can use Tags to Highlight (by Conditionally Formatting) all or some of the Notes in each separate Column of Notes that you use. You can pass Colours to change the Highlight Colour. You setup how you want Pivot Notes to Highlight Notes by adjusting the Settings in the Global Setup Section of the Code. Tags and Colours should be passed separately and equally ie. If you have 3 Columns of Notes and you want a different Tag for each Column with a different Highlight Colour then you need to pass 3 Tags and 3 Colours like this:
Private Const ConditionalFormatTag As String = ">>:feedback:!" Private Const HighlightColour As String = "14479865:16183786:15921906"
This tells Pivot Notes to use 3 Tags and for each Tag to use 3 Colours. So the first Column of Notes will be Highlighted 'Yellow' if the ">>" Tag is included in a Note. The second Column of Notes will be Highlighted 'Light Blue' if the word "feedback" is included in a Note. The third Column of Notes will be Highlighted 'Grey' if the "!" Tag is included in a Note. If none of the Tags are used the Notes will not be Highlighted
Tip: If you want to Highlight all Notes then you can pass the "*" (fuzzy) Tag
You can also use the built-in Enum Colour Codes:
Private Enum RGBLongColour rgbBrown = 3355443 rgbGrey = 15921906 rgbGreyHighlight = 15329769 rgbIceBlue = 16183786 rgbYellow = 14479865 End Enum ' // pass the Colour Codes like this Private Const HighlightColour As String = rgbGreyHighlight & ":" & rgbYellow & ":" & rgbIceBlue
If the number of Colours or the number of Tags is less than the number of Notes Columns then the Code will use as many Colours & Tags that are passed for all of the Notes Columns
This is useful when you don't want to see the repeated Item Labels as Pivot Notes requires these to build a unique Index to maintain the Notes alongside your Pivot Table. You can use Conditional Formatting to hide the Item Labels. Click in the first Cell below the Headers on your Pivot Table and on the 'Styles' Group of the 'HOME' Tab on the Ribbon, select 'Conditional Formatting', 'New Rule'
Select 'Use a formula to determine which cells to format' and add the Formula below. Adopt this Formula to fit your Range for the Column by changing all occurances of "B15" to the first Cell underneath your Headers - please note any 'Locking' of the Formula for Rows or Rows and Columns as I have done using the '$' symbol
Then press 'Format' and either change the Font Colour to White on the 'Font' Tab or add the Number Format shown below as a Custom Format on the 'Number' Tab to prevent the repeated Item Labels from being visible:
' // add this Formula to the Conditional Format =COUNTIF($B$15:$B15,B15)>1 ' // then either change the Font Colour to White or add this Number Format as a Custom Format to hide the item labels ;;;
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:
Can I Protect/Unprotect Worksheets when using Pivot Notes but still use the Pivot Tables and Slicers?
Yes, you can this by adding the following parts of Code to the RenderNotes() Subroutine. You can request a version of Pivot Notes with this example once you have purchased Pivot Notes
' #1 UNPROTECT ActiveSheet.Unprotect ' #2 UNLOCK RANGE ' - to enable unlock on range .Range(objPivotTableCustomDocNotesAddress).Locked=False ' #3 PROTECT ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowUsingPivotTables:=True
Can I use Find & Replace with Pivot Notes?
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
Can I have 10 or 20 or 50 Columns of Notes?
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
Can I supress the Warning Message informing me that: 'There's already data in [Pivot Notes.xlsm]. Do you want to replace it?'
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
Can I highlight Notes different Colours for specific Tags in all Columns?
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"
This means highlight the first Column when ">>" characters are found yellow, highlight the second Column when the word "feedback" is found in light blue and finally highlight the third Column grey when a "!" character is found
Can I use a UDF in one of the Note Columns like you would a Formula?
Yes, you can but you can only calculate what is in one of the other Columns. This is because when the Notes are rendered the UDF's are recalculated which is fine, but if, for example, you were trying to store a Date/Time Stamp this would be recalculated on the Notes Sheet every time. There is a way around Date/Time Stamps whereby you can use some small Code. Anyhow to allow a UDF simply reference it using a Formula. Suppose that I had 5 Columns of Notes from "N:R". In Cell "R13" I add the Formula:
And then add the AddValues() UDF in a new Code Module (not a Worksheet Code Module) like this - the UDF takes 2 Long Parameters and returns a Long:
Public Function AddValues(cell1 As Long, cell2 As Long) As Long AddValues = cell1 + cell2 End Function
Now the result in "R13" will be 3 if "N13" + "O13" are 1 and 2 respectively. This Formula including the UDF will be stored in the Notes Worksheet and then rendered next to the Pivot Table. You can then Sort, slice and dice the Table as you want and the values will be correct
Can I use Pivot Notes on a Pivot Table designed in "Compact" View?
Pivot Notes is designed for 'Tabular' and 'Outline' Table layouts. You can only use Pivot Notes on a Pivot Table in "Compact" View if all of your Items are unique which would be extremely unlikely but may happen depending on the design of your Pivot Table
Can I Group Items and still use Pivot Notes?
Yes you can but if you want to Store Notes at the new Grouped levels, then you will have to re-index your Pivot Table after the Grouping, so it is wise to Group if possible from the outset as you may lose Notes because they will be on different levels. Right-click on the Pivot Table Items and select "Group". Rename the Items how you want them grouped. Once you have grouped your Items, re-index your Pivot Table ensuring you add the new Field into the Pivot Notes Field Names setting
Can I change the formatting for the Pivot Notes & Pivot Notes Headers?
Yes you can do both by adjusting the Settings in the Setup section of the Code. You need to use a Style, either a Custom Style or one of the pre-defined Excel Styles. You can pass multiple Styles for each Column of Notes and Headers as a colon-delimited list ':' as the example settings below demonstrate for 3 Columns of Notes
' ## 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"
Can I Hide the "Notes" Worksheet?
Yes you can. Right-click on the Tab and select "Hide". If you want to make the Worksheet very hidden you can do this too - in the VBA Editor set the Sheet Code Module to 'xlSheetVeryHidden'. All changes use Range Objects and do not rely on selecting Ranges
I have Indexed my Pivot Table, but my Pivot Note does not appear to be stored?
You need to ensure that your Indexes are unique, for example you may have an Index like: "Total Sales/" in your Pivot Notes once but in your Pivot Table more than once, meaning that any subsequent occurance would never be stored. To avoid this you need to select Report Layout->Repeat All Label Items in the "Layout" Group on the "DESIGN" Tab of the Ribbon and Index your Pivot Table again
1. That you are not using a Date in a Pivot Table Field like: '14/03/2015' which may include the '/' backslash character with the '/' Delimiter character in the Setup Code: 'IndexDelimiter1' as the index will not be split correctly. If this is the case, then change the Delimiter to a Tilde '~' like this:
Private Const IndexDelimiterPT1 As String = "~"
2. That you haven't changed the Delimiter and then Indexed the Table without removing the original Indexes - this will cause multiple Indexes to be created and your Notes may include a previous Delimiter and therefore will never show next to the Pivot Table. It is advisable, that following an adjustment to the Delimiter that you remove the original Indexes
What options do I have to Email the File out and get people to update Notes or maintain the Notes myself for other peoples changes - can you explain how I can use Pivot Notes in the real world?
You have a couple of options when you would like to maintain Pivot Notes for changes where multiple people are involved. Here are the options:
1. You maintain a Master File and update this yourself based upon other peoples changes via Email. For example consider the scenario where you have a large File and only Email out a weekly Pivot Table without the Source to 4 or 5 people. Any of these 4 or 5 people make some comments. They then return their Files to you with their comments. You can then view these and update your Master File of Notes so that it is always representative of everyones comments ready for the next refresh
2. You Email the Original File and a person in each Department make changes, returning the original File. For example consider the scenario where you want to Email around the updated File in some sequence so that different areas of the Business make changes before finally returning the original File back to you by Email
Please remember you have purchased very affordable Software and you have not paid for a full-time Software design agency - I am but one man. Occasionally I may help with small tweaks, but these requests will be put on a much lower priority due to their nature. You have not PAID for Support, Support is 100% optional and I provide it for your convenience, so please be patient, polite and respectful
You can find the version history in the Code Module for any Macro-enabled Software or read more information below. The latest Version is always shown first
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 --- Previous Version information is not shown