Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
adjusting pivot table field names to language in use
You can change the field names manually by clicking the cell with the field
heading you want to change, then edit it in the Formula Bar. To do the same using VBA, you change the Caption property of the field, as follows: Sub AAAA() 'Change Product to ProductX ActiveSheet.PivotTables("PivotTable1"). _ PivotFields("Product").Caption = "ProductX" 'Change Sum of Quantity to Count of Quantity ActiveSheet.PivotTables("PivotTable1"). _ PivotFields("Sum of Quantity").Caption = _ "Count of Quantity" End Sub Hope this helps, Hutch " wrote: hi, i'm currently working on a pivot table using a german version of Excel 2003. Whenever I open the file using the english verison the pivot field names are stilled displayed in german. ("Summe von... " instead of "Sum of...") but the really weird stuff starts now: if I change the aggregation function from sum to count, the values displayed in the table change accordingly but the pivot table field name remains at "Summe von..." (in german!!) seems like an annoying bug :-( any ideas how to change/update the names displayed programmatically? thanx for any suggestions Verena |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
adjusting pivot table field names to language in use
Hi Hutch,
thanks for your answer! I already knew about this function. Unfortunately I won't be able to apply it like that, as the label should correspond with the actual aggregation function chosen. Unless there would be a way to retrieve information concerning the aggregation function chosen (sum/min/max/count/etc..) ? thx Verena Tom Hutchins wrote: You can change the field names manually by clicking the cell with the field heading you want to change, then edit it in the Formula Bar. To do the same using VBA, you change the Caption property of the field, as follows: Sub AAAA() 'Change Product to ProductX ActiveSheet.PivotTables("PivotTable1"). _ PivotFields("Product").Caption = "ProductX" 'Change Sum of Quantity to Count of Quantity ActiveSheet.PivotTables("PivotTable1"). _ PivotFields("Sum of Quantity").Caption = _ "Count of Quantity" End Sub Hope this helps, Hutch " wrote: hi, i'm currently working on a pivot table using a german version of Excel 2003. Whenever I open the file using the english verison the pivot field names are stilled displayed in german. ("Summe von... " instead of "Sum of...") but the really weird stuff starts now: if I change the aggregation function from sum to count, the values displayed in the table change accordingly but the pivot table field name remains at "Summe von..." (in german!!) seems like an annoying bug :-( any ideas how to change/update the names displayed programmatically? thanx for any suggestions Verena |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
adjusting pivot table field names to language in use
If you are selecting the aggregation function programmatically, then you
already know which one it is. The property you want is the Function property of the PivotField object. You can read or write this property, as follows: Sub AAAA() MsgBox ActiveSheet.PivotTables("PivotTable1"). _ PivotFields("Sum of Quantity").Function 'In Excel 2003: ' Sum returns -4157 (xlSum constant) ' Count returns -4112 (xlCount constant) ' Average reutns -4106 (xlAverage constant) ActiveSheet.PivotTables("PivotTable1"). _ PivotFields("Sum of Quantity").Function _ = xlCount End Sub Regards, Hutch " wrote: Hi Hutch, thanks for your answer! I already knew about this function. Unfortunately I won't be able to apply it like that, as the label should correspond with the actual aggregation function chosen. Unless there would be a way to retrieve information concerning the aggregation function chosen (sum/min/max/count/etc..) ? thx Verena Tom Hutchins wrote: You can change the field names manually by clicking the cell with the field heading you want to change, then edit it in the Formula Bar. To do the same using VBA, you change the Caption property of the field, as follows: Sub AAAA() 'Change Product to ProductX ActiveSheet.PivotTables("PivotTable1"). _ PivotFields("Product").Caption = "ProductX" 'Change Sum of Quantity to Count of Quantity ActiveSheet.PivotTables("PivotTable1"). _ PivotFields("Sum of Quantity").Caption = _ "Count of Quantity" End Sub Hope this helps, Hutch " wrote: hi, i'm currently working on a pivot table using a german version of Excel 2003. Whenever I open the file using the english verison the pivot field names are stilled displayed in german. ("Summe von... " instead of "Sum of...") but the really weird stuff starts now: if I change the aggregation function from sum to count, the values displayed in the table change accordingly but the pivot table field name remains at "Summe von..." (in german!!) seems like an annoying bug :-( any ideas how to change/update the names displayed programmatically? thanx for any suggestions Verena |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Field Names | Excel Discussion (Misc queries) | |||
Pivot Table field names don't match | Excel Discussion (Misc queries) | |||
Changing field names without affecting pivot table | Excel Discussion (Misc queries) | |||
Pivot table field names | Excel Worksheet Functions | |||
Setting Pivot Table Page Field Names using VBA | Excel Programming |