software

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
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 quot;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

MattThis 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)
support.microsoft.com/?id=215904

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
PeltierTech.com/
_______

quot;Mattquot; gt; wrote in message
...
gt; Hello All
gt;
gt; I'm just starting out in Excel charts so please excuse me if this is a
gt; dumb
gt; question.
gt;
gt; I'm using this download to add labels to a chart
gt; appspro.com/Utilities/ChartLabeler.htm (Thanks Rob for the use and
gt; Jon
gt; for recommending it). My chart is generated by a pivot table and the
gt; labels
gt; by a quot;live-link the data label text to the source range text. Changes in
gt; the
gt; source range text are immediately reflected in the corresponding data
gt; label.
gt;
gt; So far so good, however, when I refresh my pivot table the data labels
gt; dissappear! I've tried to record a macro to rebuild them but nothing comes
gt; up.
gt;
gt; Can someone help me set up my labels so that they won't dissapear on
gt; refresh?
gt;
gt; Thanks in Advance
gt;
gt; Matt
gt;
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(quot;%(T)(X)(A){TAB}(Flex_Percentages)quot;, True)
MsgBox quot;Please click OKquot;, vbQuestion
Call SendKeys(quot;%(T)(X)(A)(H)(HC_Percentages)quot;, True)
MsgBox quot;Please click OKquot;, vbQuestion
Call SendKeys(quot;%(T)(X)(A)(R)(Risk_Percentages)quot;, True)
MsgBox quot;Please click OKquot;, 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

Mattquot;Jon Peltierquot; wrote:

gt; This is a known issue with pivot tables and pivot charts: blink funny, and
gt; they lose their formatting. Even Microsoft suggests recording a macro to
gt; reapply the formatting, which won't help with the labels unless you can hack
gt; into Rob's add-in. Here's Microsoft's knowledge base article about the
gt; problem:
gt;
gt; Changing a PivotChart Removes Series Formatting (215904)
gt; support.microsoft.com/?id=215904
gt;
gt; - Jon
gt; -------
gt; Jon Peltier, Microsoft Excel MVP
gt; Peltier Technical Services
gt; Tutorials and Custom Solutions
gt; PeltierTech.com/
gt; _______
gt;
gt; quot;Mattquot; gt; wrote in message
gt; ...
gt; gt; Hello All
gt; gt;
gt; gt; I'm just starting out in Excel charts so please excuse me if this is a
gt; gt; dumb
gt; gt; question.
gt; gt;
gt; gt; I'm using this download to add labels to a chart
gt; gt; appspro.com/Utilities/ChartLabeler.htm (Thanks Rob for the use and
gt; gt; Jon
gt; gt; for recommending it). My chart is generated by a pivot table and the
gt; gt; labels
gt; gt; by a quot;live-link the data label text to the source range text. Changes in
gt; gt; the
gt; gt; source range text are immediately reflected in the corresponding data
gt; gt; label.
gt; gt;
gt; gt; So far so good, however, when I refresh my pivot table the data labels
gt; gt; dissappear! I've tried to record a macro to rebuild them but nothing comes
gt; gt; up.
gt; gt;
gt; gt; Can someone help me set up my labels so that they won't dissapear on
gt; gt; refresh?
gt; gt;
gt; gt; Thanks in Advance
gt; gt;
gt; gt; Matt
gt; gt;
gt;
gt;
gt;

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(quot;RangeNamequot;) _

.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
PeltierTech.com/
_______quot;Mattquot; gt; wrote in message
...
gt; Hi Jon
gt;
gt; Thanks very much for this. Not much of a hacker so haven't had a go at
gt; Rob's
gt; add-in
gt; I have managed to get it working by using SendKeys though
gt; Sub AddPercentages2RbyMonth()
gt; 'CANNOT TEST WITH THIS VBA OPEN
gt; Call SendKeys(quot;%(T)(X)(A){TAB}(Flex_Percentages)quot;, True)
gt; MsgBox quot;Please click OKquot;, vbQuestion
gt; Call SendKeys(quot;%(T)(X)(A)(H)(HC_Percentages)quot;, True)
gt; MsgBox quot;Please click OKquot;, vbQuestion
gt; Call SendKeys(quot;%(T)(X)(A)(R)(Risk_Percentages)quot;, True)
gt; MsgBox quot;Please click OKquot;, vbQuestion
gt; End Sub
gt;
gt; I have needed to inlcude all the MsgBox action as otherwise Excel kept
gt; crashing. If anyone has any ideas on why that would be I'd love to here
gt; them.
gt; Otherwise this works OK.
gt;
gt; Thanks again for clearing that up for me Jon
gt;
gt; Cheers
gt;
gt; Matt
gt;
gt;
gt; quot;Jon Peltierquot; wrote:
gt;
gt;gt; This is a known issue with pivot tables and pivot charts: blink funny,
gt;gt; and
gt;gt; they lose their formatting. Even Microsoft suggests recording a macro to
gt;gt; reapply the formatting, which won't help with the labels unless you can
gt;gt; hack
gt;gt; into Rob's add-in. Here's Microsoft's knowledge base article about the
gt;gt; problem:
gt;gt;
gt;gt; Changing a PivotChart Removes Series Formatting (215904)
gt;gt; support.microsoft.com/?id=215904
gt;gt;
gt;gt; - Jon
gt;gt; -------
gt;gt; Jon Peltier, Microsoft Excel MVP
gt;gt; Peltier Technical Services
gt;gt; Tutorials and Custom Solutions
gt;gt; PeltierTech.com/
gt;gt; _______
gt;gt;
gt;gt; quot;Mattquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hello All
gt;gt; gt;
gt;gt; gt; I'm just starting out in Excel charts so please excuse me if this is a
gt;gt; gt; dumb
gt;gt; gt; question.
gt;gt; gt;
gt;gt; gt; I'm using this download to add labels to a chart
gt;gt; gt; appspro.com/Utilities/ChartLabeler.htm (Thanks Rob for the use
gt;gt; gt; and
gt;gt; gt; Jon
gt;gt; gt; for recommending it). My chart is generated by a pivot table and the
gt;gt; gt; labels
gt;gt; gt; by a quot;live-link the data label text to the source range text. Changes
gt;gt; gt; in
gt;gt; gt; the
gt;gt; gt; source range text are immediately reflected in the corresponding data
gt;gt; gt; label.
gt;gt; gt;
gt;gt; gt; So far so good, however, when I refresh my pivot table the data labels
gt;gt; gt; dissappear! I've tried to record a macro to rebuild them but nothing
gt;gt; gt; comes
gt;gt; gt; up.
gt;gt; gt;
gt;gt; gt; Can someone help me set up my labels so that they won't dissapear on
gt;gt; gt; refresh?
gt;gt; gt;
gt;gt; gt; Thanks in Advance
gt;gt; gt;
gt;gt; gt; Matt
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;
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

Mattquot;Jon Peltierquot; wrote:

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