return fields of a cell that it belongs to in pivot table

**phoeberunner** · 07-01-2011, 02:18 AM

Hi there,

I have a question on Excel VBA.

I have a pivot table, I set EnableDataValueEditing = true to allow user to modify data area in the pivot table.

For example, in the below pivot,

Sum of Sales Column Labels
Row Labels 2010 2011 2012 Grand Total
A 7700 17510 4723 29933
B 16073 13035 4274 33382
C 13109 21500 18362 52971
Grand Total 36882 52045 27359 116286

Let say a user want to change 7700 to 7000, how could I know the changed value is the total sales of A in year 2010? Is there a pivot properties that can return the fields of a cell that it belongs to?

Thanks,
Sityee

**docMed** · 07-01-2011, 02:32 AM

I'm new here - but this is a programming related section of the forum.. isn't there another forum for instructions on non-coding related topics? =/

**phoeberunner** · 07-01-2011, 03:06 AM

my question is definitely a programming related question. I need to know the solution to help on my macro coding.

**docMed** · 07-01-2011, 03:34 AM

Well providing the macro coding you're working with would be a good start... Or just better explaining your issue. From what I can tell, you want to know if there's a way to say that the decrease from 7700 to 7000 is your total sales?

**phoeberunner** · 07-01-2011, 04:08 AM

Sorry for my ambiguous question. I didn't explain it clearly.

Actually I want to let user to change the values in pivot's data area. The changed value in data area of pivot (eg. Sum of sales) would change the data source of pivot too.

In a simple way, let say user wants to change the sales value of person A in year 2010, 7700 to 7000, any cell changed in worksheet will trigger the worksheet_change() event, which will update the data source.

My problem is, user may change any values in the pivot data area, how could I know the changed value is for person A and year 2010? Is there something like, activecell.pivotfield.name?

Hope it clarifies.

Thanks!

**docMed** · 07-01-2011, 10:08 AM

So you want to know if a specific user is making this change? for that you can use...

Excel.Application.UserName

For example;

private sub worksheet_change()
If Excel.Application.UserName = "windowsloginname" Then
GoTo RunChangeMacros
Else
Exit Sub
End if
RunChangeMacros:
'Insert your code here


end sub

As far as the year 2010, you want to simply know if the user is editing a value under a header of 2010?