VBA Pivot Table: Determining item from one field based on anotherfield item

[alexandre.eleuterio]

Hi all,

I have a Table/Listobject with a task list. I'm summarizing and performing calculations using a Pivot table. I want to use the Pivot as an interface to the user so that I can manipulate the task list data (datasource) throughcode.

For example, if the user selects a task on the pivot table, and clicks a button, the associated code would get the task name from the pivot table, anddetermine via code the task ID, so that it would be possible to change anytask data in the table (status, due date, etc). Unfortunately, I haven't figured out how to grab the task ID from the task name.

Here a sample code, I have tried out:

-------Code start---------
Dim r As Range
Dim pvt As PivotTable

On Error GoTo errpart

Set r = ActiveCell
Set pvt = Worksheets("Task by Project").PivotTables("pivot_tasks")

Idx = 0
If r.PivotField.Name <> "Task" Then
MsgBox "Selection is not a task.", vbCritical, "Error"
Else
For i = 1 To pvt.PivotFields("Task").PivotItems.Count
s = pvt.PivotFields("Task").PivotItems(i).Name
If s = r.PivotItem.Name Then Idx = i
Next i
If Idx > 0 Then
MsgBox pvt.PivotFields("UID").PivotItems(Idx).Name
End If

End If

Exit Sub
errpart:
MsgBox "Selection is not inside table.", vbCritical, "Error"
-----Code Finish----

With the code above, the ids I'm getting doesn't match the data in the datasource, which leads to the conclusion that the indexes from one Pivotitems collection from one pivotfield are not "synchronized" to the Pivotitems collection from another pivotfield.

Please, any help is very appreciated.

Regards,
Alex