Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivots with Rob Bovey's Chart Labeler
Hello All
I'm just starting out in Excel charts so please excuse me if this is a dumb question. I'm using this download to add labels to a chart http://appspro.com/Utilities/ChartLabeler.htm (Thanks Rob for the use and Jon for recommending it). My chart is generated by a pivot table and the labels by a "live-link the data label text to the source range text. Changes in the source range text are immediately reflected in the corresponding data label. So far so good, however, when I refresh my pivot table the data labels dissappear! I've tried to record a macro to rebuild them but nothing comes up. Can someone help me set up my labels so that they won't dissapear on refresh? Thanks in Advance Matt |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivots with Rob Bovey's Chart Labeler
This is a known issue with pivot tables and pivot charts: blink funny, and
they lose their formatting. Even Microsoft suggests recording a macro to reapply the formatting, which won't help with the labels unless you can hack into Rob's add-in. Here's Microsoft's knowledge base article about the problem: Changing a PivotChart Removes Series Formatting (215904) http://support.microsoft.com/?id=215904 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Matt" wrote in message ... Hello All I'm just starting out in Excel charts so please excuse me if this is a dumb question. I'm using this download to add labels to a chart http://appspro.com/Utilities/ChartLabeler.htm (Thanks Rob for the use and Jon for recommending it). My chart is generated by a pivot table and the labels by a "live-link the data label text to the source range text. Changes in the source range text are immediately reflected in the corresponding data label. So far so good, however, when I refresh my pivot table the data labels dissappear! I've tried to record a macro to rebuild them but nothing comes up. Can someone help me set up my labels so that they won't dissapear on refresh? Thanks in Advance Matt |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivots with Rob Bovey's Chart Labeler
Hi Jon
Thanks very much for this. Not much of a hacker so haven't had a go at Rob's add-in I have managed to get it working by using SendKeys though Sub AddPercentages2RbyMonth() 'CANNOT TEST WITH THIS VBA OPEN Call SendKeys("%(T)(X)(A){TAB}(Flex_Percentages)", True) MsgBox "Please click OK", vbQuestion Call SendKeys("%(T)(X)(A)(H)(HC_Percentages)", True) MsgBox "Please click OK", vbQuestion Call SendKeys("%(T)(X)(A)(R)(Risk_Percentages)", True) MsgBox "Please click OK", vbQuestion End Sub I have needed to inlcude all the MsgBox action as otherwise Excel kept crashing. If anyone has any ideas on why that would be I'd love to here them. Otherwise this works OK. Thanks again for clearing that up for me Jon Cheers Matt "Jon Peltier" wrote: This is a known issue with pivot tables and pivot charts: blink funny, and they lose their formatting. Even Microsoft suggests recording a macro to reapply the formatting, which won't help with the labels unless you can hack into Rob's add-in. Here's Microsoft's knowledge base article about the problem: Changing a PivotChart Removes Series Formatting (215904) http://support.microsoft.com/?id=215904 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Matt" wrote in message ... Hello All I'm just starting out in Excel charts so please excuse me if this is a dumb question. I'm using this download to add labels to a chart http://appspro.com/Utilities/ChartLabeler.htm (Thanks Rob for the use and Jon for recommending it). My chart is generated by a pivot table and the labels by a "live-link the data label text to the source range text. Changes in the source range text are immediately reflected in the corresponding data label. So far so good, however, when I refresh my pivot table the data labels dissappear! I've tried to record a macro to rebuild them but nothing comes up. Can someone help me set up my labels so that they won't dissapear on refresh? Thanks in Advance Matt |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivots with Rob Bovey's Chart Labeler
Ha, SendKeys. Last refuge of the desperate. When I said hack into Rob's
utility, I meant look for code you can borrow for your own program. I would use a different approach. Determine which range will be used to label which series in the chart. Use the various pivot table ranges (TableRange1 and 2, ColumnRange, RowRange, DataBodyRange, DataLabelRange, etc.) and whatever other tricks you need. Then finally, iterate through the points in the series, get the text from the corresponding cell in the range, and apply one to the other. Something like this excerpt from something I did a while back: Dim rngSource As Range Dim dlTarget As DataLabel Dim iPtsCt As Integer Dim iPtsIx As Integer For iPtsIx = 1 To iPtsCt Set rngSource = ActiveSheet.Range("RangeName") _ .Offset(iPtsIx - 1, 0).Resize(1, 1) Set dlTarget = ActiveSheet.ChartObjects(1) _ .Chart.SeriesCollection(1).DataLabels(iPtsIx) dlTarget.Text = rngSource.Value Next - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Matt" wrote in message ... Hi Jon Thanks very much for this. Not much of a hacker so haven't had a go at Rob's add-in I have managed to get it working by using SendKeys though Sub AddPercentages2RbyMonth() 'CANNOT TEST WITH THIS VBA OPEN Call SendKeys("%(T)(X)(A){TAB}(Flex_Percentages)", True) MsgBox "Please click OK", vbQuestion Call SendKeys("%(T)(X)(A)(H)(HC_Percentages)", True) MsgBox "Please click OK", vbQuestion Call SendKeys("%(T)(X)(A)(R)(Risk_Percentages)", True) MsgBox "Please click OK", vbQuestion End Sub I have needed to inlcude all the MsgBox action as otherwise Excel kept crashing. If anyone has any ideas on why that would be I'd love to here them. Otherwise this works OK. Thanks again for clearing that up for me Jon Cheers Matt "Jon Peltier" wrote: This is a known issue with pivot tables and pivot charts: blink funny, and they lose their formatting. Even Microsoft suggests recording a macro to reapply the formatting, which won't help with the labels unless you can hack into Rob's add-in. Here's Microsoft's knowledge base article about the problem: Changing a PivotChart Removes Series Formatting (215904) http://support.microsoft.com/?id=215904 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Matt" wrote in message ... Hello All I'm just starting out in Excel charts so please excuse me if this is a dumb question. I'm using this download to add labels to a chart http://appspro.com/Utilities/ChartLabeler.htm (Thanks Rob for the use and Jon for recommending it). My chart is generated by a pivot table and the labels by a "live-link the data label text to the source range text. Changes in the source range text are immediately reflected in the corresponding data label. So far so good, however, when I refresh my pivot table the data labels dissappear! I've tried to record a macro to rebuild them but nothing comes up. Can someone help me set up my labels so that they won't dissapear on refresh? Thanks in Advance Matt |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivots with Rob Bovey's Chart Labeler
Hi Jon
Apologies for my late reply! Your further response was an unexpected bonus I managed to get around my last refuge of the desperate (SendKeys) in the end. But I've added your code to my library. I'm sure it will come in useful in the future Thanks again Matt "Jon Peltier" wrote: Ha, SendKeys. Last refuge of the desperate. When I said hack into Rob's utility, I meant look for code you can borrow for your own program. I would use a different approach. Determine which range will be used to label which series in the chart. Use the various pivot table ranges (TableRange1 and 2, ColumnRange, RowRange, DataBodyRange, DataLabelRange, etc.) and whatever other tricks you need. Then finally, iterate through the points in the series, get the text from the corresponding cell in the range, and apply one to the other. Something like this excerpt from something I did a while back: Dim rngSource As Range Dim dlTarget As DataLabel Dim iPtsCt As Integer Dim iPtsIx As Integer For iPtsIx = 1 To iPtsCt Set rngSource = ActiveSheet.Range("RangeName") _ .Offset(iPtsIx - 1, 0).Resize(1, 1) Set dlTarget = ActiveSheet.ChartObjects(1) _ .Chart.SeriesCollection(1).DataLabels(iPtsIx) dlTarget.Text = rngSource.Value Next - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Matt" wrote in message ... Hi Jon Thanks very much for this. Not much of a hacker so haven't had a go at Rob's add-in I have managed to get it working by using SendKeys though Sub AddPercentages2RbyMonth() 'CANNOT TEST WITH THIS VBA OPEN Call SendKeys("%(T)(X)(A){TAB}(Flex_Percentages)", True) MsgBox "Please click OK", vbQuestion Call SendKeys("%(T)(X)(A)(H)(HC_Percentages)", True) MsgBox "Please click OK", vbQuestion Call SendKeys("%(T)(X)(A)(R)(Risk_Percentages)", True) MsgBox "Please click OK", vbQuestion End Sub I have needed to inlcude all the MsgBox action as otherwise Excel kept crashing. If anyone has any ideas on why that would be I'd love to here them. Otherwise this works OK. Thanks again for clearing that up for me Jon Cheers Matt "Jon Peltier" wrote: This is a known issue with pivot tables and pivot charts: blink funny, and they lose their formatting. Even Microsoft suggests recording a macro to reapply the formatting, which won't help with the labels unless you can hack into Rob's add-in. Here's Microsoft's knowledge base article about the problem: Changing a PivotChart Removes Series Formatting (215904) http://support.microsoft.com/?id=215904 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Matt" wrote in message ... Hello All I'm just starting out in Excel charts so please excuse me if this is a dumb question. I'm using this download to add labels to a chart http://appspro.com/Utilities/ChartLabeler.htm (Thanks Rob for the use and Jon for recommending it). My chart is generated by a pivot table and the labels by a "live-link the data label text to the source range text. Changes in the source range text are immediately reflected in the corresponding data label. So far so good, however, when I refresh my pivot table the data labels dissappear! I've tried to record a macro to rebuild them but nothing comes up. Can someone help me set up my labels so that they won't dissapear on refresh? Thanks in Advance Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XY Chart Labeler Error Message | Charts and Charting in Excel | |||
Activating a Chart object | Charts and Charting in Excel | |||
Urgent Chart Assistance | Charts and Charting in Excel | |||
Urgent Chart Questions | Excel Discussion (Misc queries) | |||
Urgent Chart Assistance Requested | Excel Discussion (Misc queries) |