i have a macro that creates a pivot table based on some user inputs an
then i do some formatting on the pivot table just for show. th
problem is that i can't maintain the formatting if the user decides t
alter the pivot table (i.e. change one of the field drop downs fro
showing "All" data to some subset of it.)

i've already tried to set the preserve formatting feature to true, bu
that didn't work .... any ideas?

thank

--
gonge
-----------------------------------------------------------------------
gonger's Profile: http://www.excelforum.com/member.php...fo&userid=3479
View this thread: http://www.excelforum.com/showthread.php?threadid=56433

You can't... It is one of the weaknesses in pivot tables. You can use the
supplied auto formats but any other formats will not be dynamic with changes
to the pivot table.

If you are using xl2002 or better then you can react to the pivot_change
event and then remove the old formats and reply the formats...
--
HTH...

Jim Thomlinson


"gonger" wrote:


i have a macro that creates a pivot table based on some user inputs and
then i do some formatting on the pivot table just for show. the
problem is that i can't maintain the formatting if the user decides to
alter the pivot table (i.e. change one of the field drop downs from
showing "All" data to some subset of it.)

i've already tried to set the preserve formatting feature to true, but
that didn't work .... any ideas?

thanks


--
gonger
------------------------------------------------------------------------
gonger's Profile: http://www.excelforum.com/member.php...o&userid=34797
View this thread: http://www.excelforum.com/showthread...hreadid=564330

that's what i feared ... oh well. thanks, Jim.


--
gonger
------------------------------------------------------------------------
gonger's Profile: http://www.excelforum.com/member.php...o&userid=34797
View this thread: http://www.excelforum.com/showthread...hreadid=564330

Right-click a cell in the pivot table, and choose Table Options
Ensure that Preserve Formatting is turned on, and AutoFormat is turned off.
Click OK

To format cells, enable selection should be turned on.
To enable selection --
From the Pivot toolbar, choose PivotTableSelect
If it's not already activated, click on Enable Selection

To format a section of a pivot table, e.g. subtotals --
Move the pointer to the left of a subtotal heading in the pivot table.
When the black arrow appears (like the one that appears when the pointer
is over a row button), click to select the subtotal rows in the pivot table.
Format the selected subtotals

gonger wrote:
i have a macro that creates a pivot table based on some user inputs and
then i do some formatting on the pivot table just for show. the
problem is that i can't maintain the formatting if the user decides to
alter the pivot table (i.e. change one of the field drop downs from
showing "All" data to some subset of it.)

i've already tried to set the preserve formatting feature to true, but
that didn't work .... any ideas?

thanks




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html