Pivot table to launch Macro
 

HI All,

No solution yesterday - can anyone help...

Once a person has made a selection on the page header part of a pivot
table, a need to call a macro that fixes formatting and the like of
other parts of the table.
(removing auto format table or preserve format under table options
doesnt work, I need to be able to call a macro.

Any suggestions welcome

Thanks

D


*** Sent via Developersdex http://www.developersdex.com ***

Pivot table to launch Macro
 

Hi Darin,

The worksheet calculate event fires when a pivot table changes. You can
launch your macro from that.

Place this code in the module for the worksheet that contains the pivot.

Private Sub Worksheet_Calculate()
call your_macro
End Sub

This will launch every time a calculation is made on that sheet. So, you
macro will run with any change in the pivot - not just page headers.

Regards,
Mike

"Darin Kramer" wrote:


HI All,

No solution yesterday - can anyone help...

Once a person has made a selection on the page header part of a pivot
table, a need to call a macro that fixes formatting and the like of
other parts of the table.
(removing auto format table or preserve format under table options
doesnt work, I need to be able to call a macro.

Any suggestions welcome

Thanks

D


*** Sent via Developersdex http://www.developersdex.com ***

Pivot table to launch Macro
 

In Excel 2002 and later versions, there's a PivotTableUpdate event that
you could use.

If you're using an older version, you can use the Worksheet_Calculate
event, as Mike suggested.

Also, in older versions, when you're selecting cells to format, use the
Pivot Table selection, instead of selecting cells with the Excel
pointer. You may have to Enable Selection first. (From the Pivot
toolbar, choose PivotTableSelect, and click on Enable Selection)

Then, before formatting cells, use the selection feature to select the
cells. For example, move the pointer to the top of a column in the pivot
table (just above the column's heading cell). When the black arrow
appears (like the one that appears when the pointer is over a column
button), click to select the column in the pivot table.

Or, use the commands on the Pivot TableSelect menu, e.g. select the
entire table, then choose to Select Data.

Then apply the formatting, and it may stick.


Darin Kramer wrote:
HI All,

No solution yesterday - can anyone help...

Once a person has made a selection on the page header part of a pivot
table, a need to call a macro that fixes formatting and the like of
other parts of the table.
(removing auto format table or preserve format under table options
doesnt work, I need to be able to call a macro.

Any suggestions welcome

Thanks

D


*** Sent via Developersdex http://www.developersdex.com ***


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html