Pivots with Rob Bovey's Chart Labeler

[Matt]

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

[Jon Peltier]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Matt]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>

[Jon Peltier]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>>
>>
>>

[Matt]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >
> >>
> >>
> >>
>
>
>