Format only part of a DataLabel in Excel 2007/PowerPoint 2007 char

  • Thread starter Thread starter SamW
  • Start date Start date
S

SamW

I am trying to custom format datalabels in a chart, for example if a
DataLabel says 12%. I want to change it to say 12% A.

This is fine, I have code (I'm actually using C# to develop but I made it
into VBA code because I think it is clearer for this forum):
ActiveSheet.ChartObjects("Chart 1").Activate
Set point = ActiveChart.SeriesCollection(6).Points(1)
point.DataLabel.Text = point.DataLabel.Text & " A"

I saw similar code here too
(http://peltiertech.com/Excel/Charts/ScientificNotation.html).

This setting of the size on a given range of text within the label is not
working for me, it is simply setting the formatting on the entire label.

I know that this is notionally possible, you can manually in
Excel/PowerPoint etc. select a range of text within a data label and change
the formatting okay. It is just that the API seems faulty, or I am
misunderstanding the API somehow?

This part works fine. However I want to set a different text format on the
A. So I try:
Dim chars As Characters
Set chars =
point.DataLabel.Characters(Len(point.DataLabel.Characters.Text), 1)
chars.Font.Size = 7
 
Text scrambled so I fixed it

SamW said:
I am trying to custom format datalabels in a chart, for example if a
DataLabel says 12%. I want to change it to say 12% A.

This is fine, I have code (I'm actually using C# to develop but I made it
into VBA code because I think it is clearer for this forum):
ActiveSheet.ChartObjects("Chart 1").Activate
Set point = ActiveChart.SeriesCollection(6).Points(1)
point.DataLabel.Text = point.DataLabel.Text & " A"
Click to expand...
 
I just tested the below (or a slight variant) in XL2003, 2007, and
2010. It worked in all the versions.

Dim chars As Characters
Set chars =
ActiveChart.SeriesCollection(1).Points(2).DataLabel.Characters(Start:=3,
Length:=1)
With chars.Font
.Underline = xlUnderlineStyleNone
.Subscript = True
.Size = 18
End With

So, at least in the above 3 versions of Excel I cannot replicate your
problem.

I am trying to custom format datalabels in a chart, for example if a
DataLabel says 12%. I want to change it to say 12% A.

This is fine, I have code (I'm actually using C# to develop but I made it
into VBA code because I think it is clearer for this forum):
ActiveSheet.ChartObjects("Chart 1").Activate
Set point = ActiveChart.SeriesCollection(6).Points(1)
point.DataLabel.Text = point.DataLabel.Text & " A"

I saw similar code here too
(http://peltiertech.com/Excel/Charts/ScientificNotation.html).

This setting of the size on a given range of text within the label is not
working for me, it is simply setting the formatting on the entire label.

I know that this is notionally possible, you can manually in
Excel/PowerPoint etc. select a range of text within a data label and change
the formatting okay. It is just that the API seems faulty, or I am
misunderstanding the API somehow?

This part works fine. However I want to set a different text format on the
A. So I try:
Dim chars As Characters
Set chars =
point.DataLabel.Characters(Len(point.DataLabel.Characters.Text), 1)
chars.Font.Size = 7
Click to expand...
Regards,

Tushar Mehta
Microsoft MVP Excel 2000-present
www.tushar-mehta.com
Excel and PowerPoint tutorials and add-ins
 
Hi Tushar,

Thanks for testing this, and partly I thought I was perhaps going mad here
but I'm not. I've confirmed that the code works okay on Excel 2007 SP1 but
Behaves as I described (formatting the entire label rather than the block of
characters selected) on Excel 2007 SP2 so this seems to be a bug that was
introduced with SP2. Actually really sanity checked this carefully by using
the identical workbook/macro in both the SP1 and SP2 installs.

Unfortuantley my solution needs to work also with PowerPoint 2007 and
therefore SP1 is a non-option because the Chart object is not exposed in the
way I need it.

Still unfortunatly searching for a solution on this one and not that close
because it seems like it probably needs MS to fix something. I'm not so happy.

Sam
 
FWIW, my 2007 test used SP2.

Obviously, something is different between your machine and mine but I
have no idea what. ;-)

Hi Tushar,

Thanks for testing this, and partly I thought I was perhaps going mad here
but I'm not. I've confirmed that the code works okay on Excel 2007 SP1 but
Behaves as I described (formatting the entire label rather than the block of
characters selected) on Excel 2007 SP2 so this seems to be a bug that was
introduced with SP2. Actually really sanity checked this carefully by using
the identical workbook/macro in both the SP1 and SP2 installs.

Unfortuantley my solution needs to work also with PowerPoint 2007 and
therefore SP1 is a non-option because the Chart object is not exposed in the
way I need it.

Still unfortunatly searching for a solution on this one and not that close
because it seems like it probably needs MS to fix something. I'm not so happy.

Sam
Click to expand...
Regards,

Tushar Mehta
Microsoft MVP Excel 2000-present
www.tushar-mehta.com
Excel and PowerPoint tutorials and add-ins
 
Interesting I tried 2 different SP2 machines that are quite differenty
configured and saw the same result one was my development machine which is
typically dirty and the other was an absolute virgin installed Vista with
Office 2007 Pro + latest hotfixes and nothing else and I got an identical
result on both.
 
Just to be really really sure, I took a working Office 2007 sp 1 virtual
machine (very clean image too), tested the macro code worked (which it did).
Applied Office 2007 sp2, rebooted the machine, ran the macro again and
verified that it failed. So, not configuration at my end I'm fairly sure.
 
Back
Top