Get the value of a RowItem in a pivot table when selected the header

[rednaxela]

Hi,

I'm new here in this forum,but what i've seen until now is the best i ever found in the web.


Here my question:

in VBA Excel 2010 i get the RowItem of a selected Cell within the pivot table with this


MsgBox "Info: " & ActiveCell.PivotCell.RowItems(1)


BUT

when i select not a data cell, but for example the "header", i mean the first cell in the respective row i got an error because PivotCell doesn't work here.


What is the correct code to get the right value?


I now that this should be possible, because when you go with the cursor over the cell, you get the mouseover info with the exact correct value.


I would be very grateful if anybody could help me with my issue.


Thanks a lot and greatings from germay!


Alexander

 

[prasaddn]

Hi Alex,


I suggest you to check the excel online help resource by selecting the words "pivotcell", "PivotCellType" in vba editor and pressing F1.


pivotcell doesn't work for header, that is correct.


To address your question, first you need to check what type of cell is the active cell in PT, XlPivotCellType can be one of these XlPivotCellType constants.

xlPivotCellBlankCell A structural blank cell in the PivotTable.

xlPivotCellCustomSubtotal A cell in the row or column area that is a custom subtotal.

xlPivotCellDataField A data field label (not the Data button).

xlPivotCellDataPivotField The Data button.

xlPivotCellGrandTotal A cell in a row or column area which is a grand total.

xlPivotCellPageFieldItem The cell that shows the selected item of a Page field.

xlPivotCellPivotField The button for a field (not the Data button).

xlPivotCellPivotItem A cell in the row or column area which is not a subtotal, grand total, custom subtotal, or blank line.

xlPivotCellSubtotal A cell in the row or column area which is a subtotal.

xlPivotCellValue Any cell in the data area (except a blank row).


example:

Sub CheckPivotCellType()


On Error GoTo Not_In_PivotTable


' Determine if cell A5 is a data item in the PivotTable.

If Application.Range("A5").PivotCell.PivotCellType = xlPivotCellValue Then

MsgBox "The cell at A5 is a data item."

Else

MsgBox "The cell at A5 is not a data item."

End If

Exit Sub


Not_In_PivotTable:

MsgBox "The chosen cell is not in a PivotTable."


End Sub


I got all these information from Microsoft Excel Help resources itself.


ActiveCell.PivotItem


Returns the name of the column heading as in raw data if active cell is row header and

name of column heading of Pivot Table if active cell is column header Or on grandtotal row lable/value.


Activecell.PivotItem


Returns value as in pivot Table / raw data if activecell is on row labels

name of column heading of Pivot Table if active cell is column header Or on grandtotal row lable/value.


Activecell.PivotTable

Returns the name of pivot table no matter where the activecell is within PT.


Regards,

Prasad