|
|||||||||
|
|||||||||
|
||||||||||||||||||||||||||||
» 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 | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
READER COMMENTS (view all comments) |
||||||||||||||||
|
No comments have been submitted. | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
Your First Business Plan: A Simple Question and Answer Format Designed to Help You Write Your Own Plan (3rd Ed)
F1 Get the Most out of Excel! The Ultimate Excel tip Help Guide
Advanced modelling in finance using Excel and VBA
Fish! A Remarkable Way to Boost Morale and Improve Results
Microsoft Word Version 2002 Step By Step (With CD-ROM)
The Accounting Game : Basic Accounting Fresh from the Lemonade Stand | ||||||||||||||||
| ||||||||||||||||
RELATED MICROSOFT EXCEL TIPS
 |
• Excel VBA books
• Accounting books
• Business Plans
• MS Office books
• Taxes books
|
Terms
and Conditions of use |
|
|
Copyright © 2003 ExcelTip.com |
|