» Automatic Report formatting using option buttons

This tip allows your report users to choose between several pivot table formats using option buttons and then updates the pivot table automatically! (Comes in handy when users have report format preferences without having to go to the pivot table toolbar and choose one everytime)

Step One:

Place an option button (or several) anywhere on your pivot table report worksheet and name them such as "Report Format 1", "Report Format 2", etc...

Do this by going to "View > Toolbars > Forms" , choose "Option button" from the toolbox and drag it onto your report.

Step Two:

Create the macro by going to "Tools > Macro > Macros ", type in a name such as "Format2" and then "Create"

Type in or cut and paste the following VB code. (Note: Make sure your pivot table is labeled the default name of "PivotTable1" by right-clicking anywhere in the pivot table, choose 'Table Options', and look at the "Name" field. Otherwise, you'll have to replace the pivot table name in the code below with the one that your pivot table is named):

Sub Format2()

' Format2 Macro

ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
ActiveSheet.PivotTables("PivotTable1").Format xlReport6

Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With


Cells.Select
Selection.Columns.Autofit
Range("A1").Select

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~

You can create different format macros using this same code and the only line you have to change is the "Format xlReport6". There are about 15 report formats to choose from. Simply change the "xlReport(number)" from 1 to 15.

The code above also automatically autofits the columns and centers the text after the report has been formatted.

Step Three:
Assign the macro to the option button by right-clicking the option button, choose "Assign Macro" and simply choose the macro that you have just created. Do this for each of your option buttons

That's it! Now when a user clicks an option button, the pivot table will automatically update to the format based on the format option that was used in the macro. Test several report styles to see which ones suit your needs and then assign them to your macros and option buttons.

Your users will be thankful and quite impressed!

Good luck!

~CJ