MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Chart Series

  Asked By: Kaua    Date: Nov 12    Category: MS Office    Views: 1637

I've written in before a couple of times with problems that members have
been good enough to solve. So here goes again ...

As part of the same application (graphing engineering data), I am trying to
draw up a graphing package that permits a user to vary the entries in the
legend box. I have had some success but have run into a problem concerning the
name of a data series.

The following code refers to a simple xy chart consisting of two data points
joined by a line. The original series name "01-Jan-06" is a date formatted
as "dd-mmm-yy". It is stored in a cell on worksheet(1) and the series is named
by adding the name through the chart wizard. The first time the series below
is run, it correctly displays the name as "01-Jan-06". No real surprises
there! However, equating the name (as shown below) to d1 (which equals
"01-Feb-06"), even formatting it appropriately (as shown), indeed setting it
equal to "01-Feb-06" results in the name of the series being "1-Feb-06" -
i.e. the leading zero is dropped. This is causing me considerable problems, so
if anyone does have any suggestions as how to restore the leading zero I would
be very grateful.

Once again, thanking you for your time and efforts

MsgBox ("Name is ") & ActiveChart.SeriesCollection(1).Name
d1 = "01-Feb-06"
ActiveChart.SeriesCollection(1).Name = d1
MsgBox ("new name ") & ActiveChart.SeriesCollection(1).Name
ActiveChart.SeriesCollection(1).Name = Format(Worksheets(1).Cells(1, 6),
MsgBox ("new name 2 ") & ActiveChart.SeriesCollection(1).Name



2 Answers Found

Answer #1    Answered By: Eustatius Bakker     Answered On: Nov 12

I don't know what is causing this odd behavior. The problem  only seems to occur
when the series  name is reassigned and the new name begins with a zero. I can
suggest a workaround, however. Try the following revised version of your code:

Sub AAAA()
Dim d1 As String
MsgBox ("Name is ") & ActiveChart.SeriesCollection(1).Name
d1$ = "01-Feb-06"
ActiveChart.SeriesCollection(1).Name = " " & d1$
MsgBox "New name 1 = " & ActiveChart.SeriesCollection(1).Name
ActiveChart.SeriesCollection(1).Name = Format(Worksheets(1).Cells(1, 6), "
MsgBox "New name 2 = " & ActiveChart.SeriesCollection(1).Name
End Sub

Basically, I am appending a space before the date  string, in both the direct
assignment statement and in the Format string (" dd-mmmm-yy"). The space doesn't
show in the result (because of the glitch), but the leading zero does.

Answer #2    Answered By: Ismet Yilmaz     Answered On: Nov 12

Regarding my problem  concerning losing the leading zero from a new name for
chart  series, I have (quite accidently) solved this. If the legend entry is
put into cell(1,1), then setting the name equal to the contents of the cell
appears to retain the leading zero. i.e. -

Worksheets(1).Cells(1, 1).NumberFormat = "dd-mmm-yy"
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(num).Name = Worksheets(1).Cells(1,1)

Sorry about the delay in replying. Again many thanks for your help.

Didn't find what you were looking for? Find more on Chart Series Or get search suggestion and latest updates.