Find current location in Pivot Table Excel Add-in VBA code example

Find current location in Pivot Table Excel Add-in VBA code example

LocationInTable

A property exposed by Range object which returns a constant, derived from XlLocationInTable Enum, helps determining current location in Pivot Table. In this article we will create one pivot table and try to identify selection within using VBA code user defined function. XlLocationInTable enum has following constants:

Constants

  1. xlRowHeader: Selection is in Row Header
  2. xlColumnHeader: Selection is in Column Header
  3. xlPageHeader: Selection is in Pivot Page Header
  4. xlDataHeader: Selection is in Data Header of the Pivot
  5. xlRowItem: Selection is in Row Item in the Pivot table
  6. xlColumnItem: Selection is in Column Item in the Pivot table
  7. xlPageItem: Selection is in Page Item of Pivot table
  8. xlDataItem: Selection is in Data Item of Pivot table
  9. xlTableBody: Selection is in Table Body of Pivot

Pivot Table

Refer above Pivot table on which we will trace selection using VBA code given below:

Code example

Public Function WhatIsMyLocation(myRange As Range) As String
    'Declare range object
    Dim oRange As Range
    'Bind selection to Range
    Set oRange = myRange

    'Put case to identify selection
    Select Case oRange.LocationInTable
        Case Is = xlRowHeader
            WhatIsMyLocation = "You are in Header Row"
        Case Is = xlColumnHeader
            WhatIsMyLocation = "You are in Column Header"
        Case Is = xlPageHeader
            WhatIsMyLocation = "You are in Page Header"
        Case Is = xlDataHeader
            WhatIsMyLocation = "You are in Data Header"
        Case Is = xlRowItem
            WhatIsMyLocation = "You are in Row Item"
        Case Is = xlColumnItem
            WhatIsMyLocation = "You are in Column Item"
        Case Is = xlPageItem
            WhatIsMyLocation = "You are in Page Item"
        Case Is = xlDataItem
            WhatIsMyLocation = "You are in Data Item"
        Case Is = xlTableBody
            WhatIsMyLocation = "You are in Table Body"
        Case Else
            WhatIsMyLocation = "Unknown selection"
    End Select
    
    'Memory cleanup
    Set oRange = Nothing
End Function

Output

Next > Paste Special a complete reference in Excel VBA code example

Leave a Reply

Your email address will not be published.