If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Format only part of a DataLabel in Excel 2007/PowerPoint 2007 char
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/...Notation.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.Cha racters.Text), 1) chars.Font.Size = 7 |
#2
|
|||
|
|||
Format only part of a DataLabel in Excel 2007/PowerPoint 2007 char
Text scrambled so I fixed it
"SamW" wrote: 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" 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.Cha racters.Text), 1) chars.Font.Size = 7 I saw similar code here too (http://peltiertech.com/Excel/Charts/...Notation.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? |
#3
|
|||
|
|||
Format only part of a DataLabel in Excel 2007/PowerPoint 2007 char
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).DataLabe l.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. On Fri, 14 Aug 2009 05:46:12 -0700, SamW wrote: 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/...Notation.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.Ch aracters.Text), 1) chars.Font.Size = 7 Regards, Tushar Mehta Microsoft MVP Excel 2000-present www.tushar-mehta.com Excel and PowerPoint tutorials and add-ins |
#4
|
|||
|
|||
Format only part of a DataLabel in Excel 2007/PowerPoint 2007
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 "Tushar Mehta" wrote: 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).DataLabe l.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. On Fri, 14 Aug 2009 05:46:12 -0700, SamW wrote: 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/...Notation.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.Ch aracters.Text), 1) chars.Font.Size = 7 Regards, Tushar Mehta Microsoft MVP Excel 2000-present www.tushar-mehta.com Excel and PowerPoint tutorials and add-ins |
#5
|
|||
|
|||
Format only part of a DataLabel in Excel 2007/PowerPoint 2007
FWIW, my 2007 test used SP2.
Obviously, something is different between your machine and mine but I have no idea what. ;-) On Sat, 15 Aug 2009 05:43:01 -0700, SamW wrote: 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 "Tushar Mehta" wrote: 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).DataLabe l.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. On Fri, 14 Aug 2009 05:46:12 -0700, SamW wrote: 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/...Notation.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.Ch aracters.Text), 1) chars.Font.Size = 7 Regards, Tushar Mehta Microsoft MVP Excel 2000-present www.tushar-mehta.com Excel and PowerPoint tutorials and add-ins Regards, Tushar Mehta Microsoft MVP Excel 2000-present www.tushar-mehta.com Excel and PowerPoint tutorials and add-ins |
#6
|
|||
|
|||
Format only part of a DataLabel in Excel 2007/PowerPoint 2007
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. "Tushar Mehta" wrote: FWIW, my 2007 test used SP2. Obviously, something is different between your machine and mine but I have no idea what. ;-) On Sat, 15 Aug 2009 05:43:01 -0700, SamW wrote: 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 "Tushar Mehta" wrote: 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).DataLabe l.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. On Fri, 14 Aug 2009 05:46:12 -0700, SamW wrote: 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/...Notation.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.Ch aracters.Text), 1) chars.Font.Size = 7 Regards, Tushar Mehta Microsoft MVP Excel 2000-present www.tushar-mehta.com Excel and PowerPoint tutorials and add-ins Regards, Tushar Mehta Microsoft MVP Excel 2000-present www.tushar-mehta.com Excel and PowerPoint tutorials and add-ins |
#7
|
|||
|
|||
Format only part of a DataLabel in Excel 2007/PowerPoint 2007
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. "SamW" wrote: 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. "Tushar Mehta" wrote: FWIW, my 2007 test used SP2. Obviously, something is different between your machine and mine but I have no idea what. ;-) On Sat, 15 Aug 2009 05:43:01 -0700, SamW wrote: 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 "Tushar Mehta" wrote: 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).DataLabe l.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. On Fri, 14 Aug 2009 05:46:12 -0700, SamW wrote: 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/...Notation.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.Ch aracters.Text), 1) chars.Font.Size = 7 Regards, Tushar Mehta Microsoft MVP Excel 2000-present www.tushar-mehta.com Excel and PowerPoint tutorials and add-ins Regards, Tushar Mehta Microsoft MVP Excel 2000-present www.tushar-mehta.com Excel and PowerPoint tutorials and add-ins |
Thread Tools | |
Display Modes | |
|
|