Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Pivot Table: Determining item from one field based on anotherfield item
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) through code. 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, and determine via code the task ID, so that it would be possible to change any task 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Help Using Calculated Field or Item in Pivot Table | Excel Worksheet Functions | |||
Pivot Table Calculated Field / Item | Excel Programming | |||
Overwrite field item name in a Pivot table | Excel Discussion (Misc queries) | |||
Pivot Table Calculated field item | Excel Discussion (Misc queries) | |||
Delete item from Pivot Table field? | Charts and Charting in Excel |