Pivots with Rob Bovey's Chart Labeler

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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/
_______
 
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
 
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/
_______
 
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
 
Back
Top