Understand Pivot Cell in Excel Add-in VBA example

PivotCell

A cell within PivotTable. PivotCell offers various methods and properties by which user can play with. In this article we have a simple Pivot table and will try to understand PivotCell object with example:

Data source

Analysis

We will put code to get followings:

1. Get the position of user selection of a Pivot cell. In above example the selection is made to 5th item within Pivot Table
2. Get the Pivot name using Parent
3. Identify the type of pivot cell

Code example

Public Sub PivotCell()
    'Declare Range object
    Dim oPivotRange As Range
    
    'Bind Pivot reference
    Set oPivotRange = Selection
    
    'Create Pivot Cell object
    Dim oPivotCell As PivotCell
    
    'Bind pivot object to Pivot cell
    Set oPivotCell = Selection.PivotCell
    
    'print selection position in pivot table
    Debug.Print "Selected cell is located at " & oPivotCell.PivotRowLine.Position & "th Position in Pivot"
    
    'Print Pivot table name
    Debug.Print oPivotCell.Parent.Name

    'Check selection type
    Select Case oPivotCell.PivotCellType
        Case XlPivotCellType.xlPivotCellBlankCell:
            Debug.Print "Selection at Blank cell"
        Case XlPivotCellType.xlPivotCellCustomSubtotal:
            Debug.Print "Selection at Custom Subtotal row"
        Case XlPivotCellType.xlPivotCellDataField:
            Debug.Print "Selection at Data Field"
        Case XlPivotCellType.xlPivotCellDataPivotField:
            Debug.Print "Selection at Data Pivot Field"
        Case XlPivotCellType.xlPivotCellGrandTotal:
            Debug.Print "Selection at Grand Total row"
        Case XlPivotCellType.xlPivotCellPageFieldItem:
            Debug.Print "Selection at Page Field Item"
        Case XlPivotCellType.xlPivotCellPivotField:
            Debug.Print "Selection at Piviot Field"
        Case XlPivotCellType.xlPivotCellPivotItem:
            Debug.Print "Selection at Pivot Item"
        Case XlPivotCellType.xlPivotCellSubtotal:
            Debug.Print "Selection at Subtotal row"
        Case XlPivotCellType.xlPivotCellValue:
            Debug.Print "Selection at Value"
        Case Else
            Debug.Print "Unknown selection"
    End Select
    
    'Memory Cleanup
    Set oPivotCell = Nothing
    Set oPivotRange = Nothing
End Sub

Output

Selected cell is located at 5th Position in Pivot
ProductTable
Selection at Pivot Item

Next >> Understand PivotTable in Excel a complete reference using code

Share on Facebook

Tweet