?
Solved

Excel VB.net Add-in  Find Pivot header based on pivot cell

Posted on 2013-01-24
3
Medium Priority
?
599 Views
Last Modified: 2013-02-03
Hi

In the following pivot what VBA code would I use to find the header (eg Goal, Status, Task) based on the cell I am in. The pivot can change so I need to be able to do this dynamically.

I use the following code to get the pivot cell to the left:

    Function Get_Pivot_Cell_to_Left() As String
        Try
            'Gets the pivot cell value to left even where label is blank
            Dim pCell As Excel.PivotCell
            'pCell = Globals.ThisAddIn.Application.ActiveCell.PivotCell
            pCell = Globals.ThisAddIn.Application.ActiveCell.PivotCell
            If pCell.PivotField.Position > 1 Then
                Get_Pivot_Cell_to_Left = pCell.PivotRowLine.PivotLineCells(pCell.PivotField.Position - 1).PivotItem.Caption
            End If

        Catch ex As Exception
            MsgBox("Problem getting Task data " & ex.Message)
            Get_Pivot_Cell_to_Left = ""
        End Try
    End Function

1
0
Comment
Question by:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38813467
Does this code return you header of the left side?
0
 

Accepted Solution

by:
Murray Brown earned 0 total points
ID: 38813522
Hi

Yes it does.

I can get anu of the three headers using
Get_Pivot_Field = pCell.PivotField.Name

but I am still struggling to get the two top one "Project:Site", and the other
0
 

Author Closing Comment

by:Murray Brown
ID: 38848276
this post answered the original question
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question