Format of pivot table total

[treva31]

Is there a way to set the format of a Total field in vba?

This code sets the format of the underlying field but not the total of it:

Forms("Form Name").PivotTable.Activeview.DataAxis.FieldSets(0).Fields(0).DetailBackColor = "Red"

 

[vbaInet]

Maybe it is. I've not used Pivot tables in a long time but let's see your db.

 

[treva31]

Here it is.

So if you put the previosuly quoted code in the immediate window and double click a number, it expands and the ones underneath are red.

But how do I get the totaled ones in red?
ie the first number: $31,663.11

Maybe it is. I've not used Pivot tables in a long time but let's see your db.

 

[vbaInet]

Like so:

Forms("Form Name").PivotTable.Activeview.TotalBackColor = vbRed

 

[treva31]

Ah excellent thank you!

Can I also change the format of the totals to not show any cents?

 

[vbaInet]

Forms("Form Name").PivotTable.ActiveView.Totals(0).NumberFormat = "[$$-C09]#,##0;[red]-[$$-C09]#,##0"

 

[treva31]

Beautiful, thank you

 

[vbaInet]

You're welcome!

 

[berntis]

Hi!

This is similar to what i want to do! But I am a bit of a newbie, and i cannot figure out where you have this code for the formatting of the Pivot table?

I can't find anything in the VBA part... This is probably a noob question but where is this code located?

 

[treva31]

If you press ALT+F11 you get to the VBA window.

Double click on the form name on the left.

In the main window is where you put your macros.

Mine code was on this event:

Private Sub DESPATCH_VALUE_BeforeUpdate(Cancel As Integer)
 
Me.PivotTable.Activeview.DataAxis.FieldSets(0).Fields(0).DetailBackColor = "Green"
Me.DetailBackColor = "Green"
Me.Form.PivotTable.Activeview.DataAxis.FieldSets(0).Fields(0).DetailBackColor = "Green"
Me.PivotTable.PivotSelect "'Output ($)'", xlDataOnly
Selection.NumberFormat = "$#,##0"
 
End Sub

 

[berntis]

Hello and thank you for your reply!

I've tried that, but no forms or anything shows up on the left...
Is there restrictions or something on the database?
Or perhaps there is something wrong with the file?(seems unlikely considering i'm able to open it...)

regards, Berntis

 

[berntis]

Anybody have any ideas?