Modifying Pivot Tables

An Excel pivot table is designed to be flexible. For example, users can easily change a row field to a column field and hide certain items in the pivot table that are not relevant to their current needs. You might want to provide your own interface to make it even easier for the user to make certain pivot table changes. The example in this section presents a pivot table that can be controlled by a series of OptionButtons and two CheckBox controls, as shown in Figure 17-7.

The pivot table contains four additional calculated items (Q1, Q2, Q3, and Q4), which compute quarterly totals. The VBA code that's executed when OptionButtonl (Months Only) is selected is shown in Listing 17-5. The procedure is straightforward and similar to the event handler procedures for the other OptionButtons.

Listing 17-5: Responding to a User Request to Adjust a Pivot Table Option

Private Sub OptionButton1_Click() ' Months only

Application.ScreenUpdating = False

With ActiveSheet.PivotTables

PivotItems

"Jan

")

.Visibl

e

= True

PivotItems

"Feb

")

.Visibl

e

= True

PivotItems

"Mar

")

.Visibl

e

= True

PivotItems

"Apr

")

.Visibl

e

= True

PivotItems

"May

")

.Visibl

e

= True

PivotItems(

"Jun

")

.Visibl

e

= True

PivotItems(

"Jul

")

.Visibl

e

= True

PivotItems(

"Aug

")

.Visibl

e

= True

PivotItems(

"Sep

")

.Visibl

e

= True

PivotItems(

"Oct

")

.Visibl

e

= True

PivotItems(

"Nov

")

.Visibl

e

= True

PivotItems(

"Dec

")

.Visibl

e

= True

PivotItems(

"Q1"

).

Visible

=

False

PivotItems(

"Q2"

).

Visible

=

False

PivotItems(

CO

).

Visible

=

False

PivotItems(

"Q4"

).

Visible

=

False

1).PivotFields("Month")

End With End Sub

[IF

noddy pivot.xls

a

A

B

c

D E

F

G

_

5

O Months Only ® Quarter Totals Only O Months and Quarter Totals

1 Show Column Grand Totals 0 Show Row Grand Totals

6

7

Region

|(AII) t|

8 9

Sales

Month -r|

10

SalesRei>

01

02

03 04

Grand Total

14

Amy Bob Chuck Douq

41,817 30,700 35,204 51,250

41,238 42,891 36,303 51,859

45,122 45,691 40,357 39,707 35,689 30,040 53,611 51,736

173,886 101,715 137,436 200,404

16

-

1 H < ► nummary/Data/ |<| | >||

Figure 17-7: The user can use the controls to adjust the pivot table.

Figure 17-7: The user can use the controls to adjust the pivot table.

The CheckBox controls simply toggle the display of the grand totals. These event handler procedures follow:

Private Sub CheckBox1_Click() ' Column Grand Totals

Application.ScreenUpdating = False

ActiveSheet.PivotTables(1).ColumnGrand = CheckBoxl.Value End Sub

Private Sub CheckBox2_Click() ' Row Grand Totals

Application.ScreenUpdating = False

ActiveSheet.PivotTables(1).RowGrand = CheckBox2.Value End Sub

Pivot tables, of course, can be modified in many other ways. As I mention, the easiest way to create VBA code that modifies pivot tables is to turn on the macro recorder while you make the changes manually. Then adjust the code and copy it to the event handler procedures for your controls.

Chapter 18

0 0

Post a comment