A squirrely one for chart geniuses :)

OK here's the problem.

I have a multipage Excel spreadsheet with a consolidation
chart page at the end. The chart page grabs data from
individual store pages and displays sales data using a
dropdown box for store names, and the INDIRECT function to
copy data from the store page to the line chart on the
chart page.

Now each of these store have also had a particular
security system installed at the store. The chart also
shows the installation date by referencing a VLOOKUP table
of store names and installation date.

So far, so good.

The charts have as their X axis the months in which
stocktakes were done ....and this axis is not uniform
between charts ... one chart may range from Jan 00 to Sep
03 (with 6 stocktakes) whilst another may range from Jun
02 to Feb 04 (with 2 stocktakes). Now even though the
system install date is displayed in a text box on the
chart, the Powers that Be within this organisation
apparently cannot read text, as they want a text box with
an arrow pointed at the place in the chart where the
security system install month would appear.

Confusing, ain't it? And frankly I not only have no idea
how to do it... I don't even know if it CAN be done.

Help?

I've never worked out what the ApplyPictToFront Property is for, but it
sound like it may be what you're after. Doubtless one of the gurus will
know.

An easy way to do it would be to add an arrow character as the point's
data label, but you're fairly limited in appearance options, eg

Sub add_arrow1()
ActiveChart.SeriesCollection(1).Points(12).HasData Label = True
ActiveChart.SeriesCollection(1).Points(12).DataLab el.Text = Chr(175)
ActiveChart.SeriesCollection(1).Points(12).DataLab el.Font.Name =
"Symbol"
End Sub


If you want to add a line, this code is clumsy, but sort of works.
Assuming you know which series( series_num) and point of the series
(point_num) you want to add the arrow to, it calculates the distance of
that point's data label from the left hand side of the chart, and adds
an arrow terminating at that point. Its not precise, but fairly close.

I'm sure there will be more elegant solutions offered.

Sub add_arrow2()
line_height = 22
series_num = 1
point_num = 5

has_label =
ActiveChart.SeriesCollection(series_num).Points(po int_num).HasDataLabel
If has_label = False Then
ActiveChart.SeriesCollection(series_num).Points(po int_num).HasDataLabel
= True
txt =
ActiveChart.SeriesCollection(series_num).Points(po int_num).DataLabel.Text
fonsize =
ActiveChart.SeriesCollection(series_num).Points(po int_num).DataLabel.Font.Size
ActiveChart.SeriesCollection(series_num).Points(po int_num).DataLabel.Text
= "l"
ActiveChart.SeriesCollection(series_num).Points(po int_num).DataLabel.Font.Size
= 1

end_left =
ActiveChart.SeriesCollection(series_num).Points(po int_num).DataLabel.Left
+
Len(ActiveChart.SeriesCollection(series_num).Point s(point_num).DataLabel.Text)
/ 2
end_top = ActiveChart.Axes(xlCategory).Top

ActiveChart.SeriesCollection(series_num).Points(po int_num).DataLabel.Text
= txt
ActiveChart.SeriesCollection(series_num).Points(po int_num).DataLabel.Font.Size
= fonsize


If has_label = False Then
ActiveChart.SeriesCollection(series_num).Points(po int_num).HasDataLabel
= False

start_top = end_top - line_height
start_left = end_left - line_height / 2

ActiveChart.Shapes.AddLine(start_left, start_top, end_left,
end_top).Select
Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle
Selection.ShapeRange.Line.EndArrowheadLength =
msoArrowheadLengthMedium
Selection.ShapeRange.Line.EndArrowheadWidth = msoArrowheadWidthMedium
End Sub


---
Message posted from http://www.ExcelForum.com/

Create a 2nd series with just the one point that corresponds to the
security system install date. Create the appropriate data label.

To create and use a downward pointing arrow, do the following:

Create the arrow: Use the Drawing toolbar to create a downward pointing
arrow. Duplicate it. Position the two so that the bottom of one just
touches the top of the other. For the lower arrow, change both the Fill
and Line to None.

Use the arrow: Group both objects, and copy the group. Select the one
point dummy series added to the chart and paste.

--
Regards,

Tushar Mehta
MS MVP Excel 2000-2004
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office


In article ,
says...
OK here's the problem.

I have a multipage Excel spreadsheet with a consolidation
chart page at the end. The chart page grabs data from
individual store pages and displays sales data using a
dropdown box for store names, and the INDIRECT function to
copy data from the store page to the line chart on the
chart page.

Now each of these store have also had a particular
security system installed at the store. The chart also
shows the installation date by referencing a VLOOKUP table
of store names and installation date.

So far, so good.

The charts have as their X axis the months in which
stocktakes were done ....and this axis is not uniform
between charts ... one chart may range from Jan 00 to Sep
03 (with 6 stocktakes) whilst another may range from Jun
02 to Feb 04 (with 2 stocktakes). Now even though the
system install date is displayed in a text box on the
chart, the Powers that Be within this organisation
apparently cannot read text, as they want a text box with
an arrow pointed at the place in the chart where the
security system install month would appear.

Confusing, ain't it? And frankly I not only have no idea
how to do it... I don't even know if it CAN be done.

Help?