Keep Pivot Table custom chart type

I'm creating a pivot table custom chart type "Line - Column on 2 Axes". The 
chart looks great when I create it. The problem is when the user does 
anything to the chart (filters the data, adds a new flield of data, etc.), 
the chart changes to where both data series are displayed as bar charts and 
the second axis is gone. I can't find an event that fires when they modify 
the chart, so I don't know that I can programatically change it back. Is 
there a way to keep it from changing in the first place? I'm using Excel 2003.

Thanks!
0
JeffM (17)
4/8/2005 7:09:03 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
472 Views

Similar Articles

[PageSpeed] 19

Loss of formatting is a known problem with pivot charts. There's 
information in the following MSKB article, suggests recording a macro as 
you apply the formatting:

Changing a PivotChart Removes Series Formatting
       http://support.microsoft.com/?id=215904



Jeff M wrote:
> I'm creating a pivot table custom chart type "Line - Column on 2 Axes". The 
> chart looks great when I create it. The problem is when the user does 
> anything to the chart (filters the data, adds a new flield of data, etc.), 
> the chart changes to where both data series are displayed as bar charts and 
> the second axis is gone. I can't find an event that fires when they modify 
> the chart, so I don't know that I can programatically change it back. Is 
> there a way to keep it from changing in the first place? I'm using Excel 2003.
> 
> Thanks!


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

0
dsd1 (5911)
4/8/2005 7:37:55 PM
Thanks for the reference. The problem is that I've written an application 
that sits on a user's desktop. When the user changes the data, what event is 
raised that I can use to kick off the macro?

Thanks!

"Debra Dalgleish" wrote:

> Loss of formatting is a known problem with pivot charts. There's 
> information in the following MSKB article, suggests recording a macro as 
> you apply the formatting:
> 
> Changing a PivotChart Removes Series Formatting
>        http://support.microsoft.com/?id=215904
> 
> 
> 
> Jeff M wrote:
> > I'm creating a pivot table custom chart type "Line - Column on 2 Axes". The 
> > chart looks great when I create it. The problem is when the user does 
> > anything to the chart (filters the data, adds a new flield of data, etc.), 
> > the chart changes to where both data series are displayed as bar charts and 
> > the second axis is gone. I can't find an event that fires when they modify 
> > the chart, so I don't know that I can programatically change it back. Is 
> > there a way to keep it from changing in the first place? I'm using Excel 2003.
> > 
> > Thanks!
> 
> 
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
> 
> 
0
JeffM (17)
4/8/2005 7:51:03 PM
Assuming the pivot chart is on a chart sheet, you can use the 
Chart_Calculate event.


Jeff M wrote:
> Thanks for the reference. The problem is that I've written an application 
> that sits on a user's desktop. When the user changes the data, what event is 
> raised that I can use to kick off the macro?
> 
> Thanks!
> 
> "Debra Dalgleish" wrote:
> 
> 
>>Loss of formatting is a known problem with pivot charts. There's 
>>information in the following MSKB article, suggests recording a macro as 
>>you apply the formatting:
>>
>>Changing a PivotChart Removes Series Formatting
>>       http://support.microsoft.com/?id=215904
>>
>>
>>
>>Jeff M wrote:
>>
>>>I'm creating a pivot table custom chart type "Line - Column on 2 Axes". The 
>>>chart looks great when I create it. The problem is when the user does 
>>>anything to the chart (filters the data, adds a new flield of data, etc.), 
>>>the chart changes to where both data series are displayed as bar charts and 
>>>the second axis is gone. I can't find an event that fires when they modify 
>>>the chart, so I don't know that I can programatically change it back. Is 
>>>there a way to keep it from changing in the first place? I'm using Excel 2003.
>>>
>>>Thanks!
>>
>>
>>-- 
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
> 


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

0
dsd1 (5911)
4/8/2005 7:58:31 PM
Reply:

Similar Artilces:

Sales Cycle Flow Chart
I've looked but have only found pieces of the sales cycle flow chart. Can anyone send me the link that shows the entire sales cycle flow chart? I found some really nice graphics for this in the eLearning section of PartnerSource. If you do the Online eLearning, you can download the lessons for offline viewing, then save the images in the lessons. If memory serves, you'll find all sorts of great stuff in the MSCRM 3.0 - Application lessons. "Troy" wrote: > I've looked but have only found pieces of the sales cycle flow chart. Can > anyone send me the link tha...

Can I keep an autoshape tool selected?
For example, if I want to draw multiple lines on a page using the line tool is there a way to just draw, draw, draw instead of going back to get the tool after each "draw". Thanks, Frank wrote: > For example, if I want to draw multiple lines on a page using the > line tool is there a way to just draw, draw, draw instead of going > back to get the tool after each "draw". > Thanks, In Word 2003 or earlier, you can double-click the line, arrow, rectange, or oval tool buttons on the Drawing toolbar (but not the other shapes that are only in the Aut...

Intelligent Pivot Table with Large Data Sets
Pivot Tables are great with small datasets. Does anyone have suggestions of products that can datasource from a ODBC source and manage large datasets (1M+ rows), visualizing the dat in a crosstab/pivottable format ? Unfortunately the underlying logic in the PivotTable/MSquery link isn' too intelligent in that is always wants to read the whole darn databas table before even presenting a field list. I assume this is due to th abstraction of the data created by sticking MS/Query inbetween th Excel pivot table service and the actual datasource? Seems pretty simply from a conceptual POV: 1. ...

"grouping" a table to prevent change from users
Hi, I would like to group an entire table with a wdContentControlGroup type of Content Control to prevent editing from users except in some wdContentControlRichText type of Content Control. I'm having problem in passing the right Range of the table. If I use: With ActiveDocument Set r = .Tables(1).Range Set objcc = .ContentControls.Add(wdContentControlGroup, r) End With an error "5224" "Wrong selection" arise. If I use: With ActiveDocument .Tables(1).Select Set objcc = .ContentControls.Add(wdCo...

Unruly chart
I am charting monthly call volumes in a line graph in which I have 7 series (a different line of business). Each series takes 12 cells of data, corresponding to each month of the year, which I have in a different worksheet. Usually I can hit Control and individually click the different cells so that they are all included in the Values, but for some reason this time, I get to the 10th month and it won't let me click on the cell. I click one more time and it starts me over, erasing the previous 9 cells that I have clicked on. Anyone know what's going on and how I can fix it? ...

Potential customer field in Quote
Hi, Is there a way to make the Potential customer field in Quote entity as NOT required? Thank you in advance for any suggestions, crsb The entire sales process from opportunity up to the invoice requires a customer. If you don't need the field, create a dummy account and set the customer id to this dummy account in OnLoad. -- Michael H�hne, Microsoft Dynamics CRM MVP CRM Blog on http://www.stunnware.com ---------------------------------------------------------- "crsb" <srinivasa.bharadwaj@gmail.com> schrieb im Newsbeitrag news:76d7f1b1-c86f-470b-bbec-0329d9d29f...

2 pivot tables on one tab
Is it possible to put 2 Pivot tables on the same tab in my workbook? Or does anyone have any ideas on what would be the best way to chart Capital Pircahses? I have 5 coums and 4 rows. The rows are per division, and the columns are for the disposal amount, gain or loss, book value, depreciation & purchase price. I already have one pivot table in my report. Would this be too repeatative? Thanks You can put multiple pivot tables onto a worksheet. If the second one is based on the same data, select and copy the first, select a cell some distance away, and paste it. Each pivot table...

Pivot Table Data Ranges
I just upgraded to excel2000, and am finding out that keyboard shortcuts used in 97 do not exist in 2000. Does anyone know of a KEYBOARD shortcut to modify, expand and or create data ranges in the dialog box for entering the range for the pivot. I have many data 'lists' which are added to each month, and when I want to update the corresponding pivot table, I used to simply hit "shift" + "end" + "down arrow" to automatically go to the last populated cell in the list. In 2000, I either have to enter in the cell addy's manually or use the mouse...

Pivot Table Freezes
I have many pivot tables off a large database and they periodically freeze when I refresh the data. Only solution is to crash out. Any ideas, comments, solutions out there. The machine has been checked out OK. I work on a network - checked out OK. Maybe this is the problem: XL2000: PivotTable Updated Slowly with OLAP Data Source http://support.microsoft.com/default.aspx?scid=237469 Jimbo wrote: > I have many pivot tables off a large database and they periodically freeze > when I refresh the data. Only solution is to crash out. Any ideas, comments, > solutions out there....

Find cell numbers in a table so I can multiply
Hello, I have a word document and in the documents header there is a table. This table a has diferent number of cells in each row like: Row one: 2 cells Row two: 4 cells Row three: 2 cells Row four: 10 cells Row five: 2 cells What I wanted to do is multipy 3 cells together thats in row four and show the total in the same row. I have a number in the 3rd cell that needs * by the number in the fith cell that needs * by the number in the seventh cell and totaled in the 9th cell. I know I have to select the ninth cell and select table/formula then what? -- Thanks, Chad I ...

Keeping Validation References When Breaking out Spreadsheets Using
Hello, I am using a version of Ron Debruin’s macro that breakouts spreadsheets into separate spreadsheets using a filter on a selected column. The issue that I am having is that I have a series of validation references located in the main sheet in hidden rows (rows 1-14 are hidden). I need to be able to retain these references in all the newly created sheets and retain the fixed references. How do I do this? Thanks in advance. Modified Ron Debruin Macro Sub FPR_Breakout_Worksheets() Dim calcmode As Long Dim ws1 As Worksheet Dim ws2 As Worksheet Dim WS...

Sizing Data Tables on a chart
When I have a data table attached to a chart and size the chart, the data table does not size proportionately ...

URGENT: Creating a chart (Getting the correct input and output) unable to figure out
Hi, I have been trying to create this chart since a long time now and go no clue what am donig wrong. I have some machines in my company and all of them have a end of life for example: Machine 1, at location A with End of Life in 2004 Machine 2, at location B with End of Life in 2008 I want the years on x-axis and the machine locations on y-axis. Afte that I want horizantal lines representing machines which also show th end of life. For a better understanding, see the attached excel file. I have als created an image of the chart I'm looking to draw. Please advise. Thanks. Ker ...

Custom auto-sorting...possible?
Is there a way to have excel auto-sort (cutom) by criteria in 3 columns? I think it might be easier to explain by viewing the workbook http://poolgod.zftp.com/predictions.xls. The picture shows how I need it to sort. I found some information on auto-sort in help and also on the net, but I don't know how to incorporate the multiple criteria or if it's even possible. Any ideas? Thanks, Steve -- Pools ------------------------------------------------------------------------ Pools's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35961 View this thread: ht...

Operator Overload between different types
I'm trying to implement the overload for two different kinds of objects. I tryed for example: Dog1 + Dog2; Dog Dog::operator+(Dog& s) { return this->nBau + s.nBau; } it works fine. I need something... Dog1 + Cat1; ??? Dog::operator+(Cat& s) { return ???; } How can i implement my overload between different types and what type of object must return? I can only compile my program only specifing "return 0" in overload function but it doesn't work. Maybe i must implement overload function as static function outside class Dog? Some help please. My final n...

Custom MS Excel Charts
What technologies do I need to be able to develop custom charts for MS Excel, are there any nice friendly APIs/libraries. Thanks Umair ...

Small problem with a line chart
Hi I have a smal problem with a line chart. The chart shows progress of my teams league points over a season, the data is derived from a series of rows which I fill in after a game. In the data, I am using COUNTBLANK to have a blank cell until the row contains data EG =IF(COUNTBLANK(P53:Y53)>0,"",((P53+U53)*3)+Q53+V53) The problem is that the data series assumes unfilled rows (IE the future unplayed games) are zero, so there is a line that connects from the top of my line down to the zero on my X axis. I would just like the line to stop at the last value. Can anyone suggest a w...

Q: Charting with two variables on the Y-axis
Using Excel 2000: Hey all, I have two tables and want a line graph based on one row, but with the values of another row: Votes: ------ Region1: 10, 10, 15 Total: 50, 60, 100 Satisfaction Rates: ------------------- Region1: 60%, 80%, 45% Overall: 50%, 90%, 50% I want a line graph that compares the satisfaction rates of region 1 vs. overall (ie, it will show two lines, one for region1, one for overall). That's pretty straightforward. However, what I also want is, for Region1, to show the vote counts as values above (or below) the datapoints for the region 1 line. Can this be ...

DLL tell what type of application is running.
I'm writing a general purpose error handler that will be used by Windows Apps, Web Apps, Window's Services, ... In other words - just about any program will be calling it. Part of this error routine would be to put up a message box if it's a windows app but only if it's a windows app. How can I make sure that the application that is running is actually a Window's app. I currently am passing in parameters to display the message if appropriate but I want to make sure that somebody doesn't by accident call the routine with a message to display for something ...

Custom print comments in Excel 2007
Hi there There are two ways to print comments in Excel, either the WYSIWYG way (little stacked boxes over the actual worksheet) or as separate pages. Problem with the separate pages way: the comments get printed as Cell: B9 Comment: User name: Comment... Now, the cell number is not necessarly the easiest way to locate a specific cell. My question: would it be possible to replace the cell number with, for example, the content of the cell or the content of the neighboring cell? I'm not an expert in VBA programming, but if someone could point out where I should start to customiz...

Excel Pivot tables and charts
How do I retain pivot chart formats that I have made (esp. column colours) after refreshing the data on the source pivot table? Hi, Loss of chart formatting wwith regards to pivot charts is a know problem, which requires VBA to re apply recorded formatting. XL2000: Changing a PivotChart Removes Series Formatting (Q215904) <http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q215904 > Cheers Andy Pauleze wrote: > How do I retain pivot chart formats that I have made (esp. column colours) > after refreshing the data on the source pivot table? -- Andy Pope, Microsoft MVP...

Customization guide #2
Anyone out there want to give up a copy of the developers guide? My store is on a very tight budget, and has a hard time calling support everytime we want to see what can be done with RMS. If you want to email it anonymously, I won't say anything..... Thanks! Is there a developer's guide? Every time I called MS about development information all I get is routed to a salesperson or told we can put you in touch with "a partner" I even went thru the route of trying to become a partner in order to have more dev materials... No luck so far. "New RMS User" <N...

How to run a make table qry from switchboard
I have built a switchboard that runs reports. All of the reports are built from queries which have a common table. That table is built from a make table query. Question: How can I run the make table query from the switchboard so that my data is up to date? THANKS OK...I figured out how to get the Query to run...So now, how do I Supress the question windows and get them to automatically select "Yes" so the Make table will run without the user having to select Yes to delete and Yes to run? Thanks "Rachael" wrote: > I have built a switchboar...

Use Rule to Fire Off Net Send Type Messages
Outlook 2003 I would like to know if there is a way to use an Outlook rule to send something like a Net Send message to another computer. One possible stumbling block is that we have the Messenger service disabled on our machines, so Net Send itself will not work unless we enable that service. For the few machines that the message would need to go to, we may be able to turn on the Messenger service. Nonetheless, I would like to know if there are any ways to accomplish this without needing to enable the Messenger service. You may be wondering why we want to do this. We have a mailbo...

Aggregate data types mismatch
Using reporting services 2008. Getting a really weird error when calculating a variance between a budget and actual amount in the report. Both the underlying fields are of data type 'money', but when performing the calc in the report writer, i am getting the following error: [rsAggregateOfMixedDataTypes] The Value expression for the textrun 'Variance_MTD_ActualvsBudget1.Paragraphs[0].TextRuns[0]' uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a si...