A squirrely one for chart geniuses :)

  • Thread starter Thread starter Andrew Finlayson
  • Start date Start date
A

Andrew Finlayson

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 i
sound like it may be what you're after. Doubtless one of the gurus wil
know.

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

Sub add_arrow1()
ActiveChart.SeriesCollection(1).Points(12).HasDataLabel = True
ActiveChart.SeriesCollection(1).Points(12).DataLabel.Text = Chr(175)
ActiveChart.SeriesCollection(1).Points(12).DataLabel.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 serie
(point_num) you want to add the arrow to, it calculates the distance o
that point's data label from the left hand side of the chart, and add
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(point_num).HasDataLabel
If has_label = False The
ActiveChart.SeriesCollection(series_num).Points(point_num).HasDataLabe
= True
txt
ActiveChart.SeriesCollection(series_num).Points(point_num).DataLabel.Text
fonsize
ActiveChart.SeriesCollection(series_num).Points(point_num).DataLabel.Font.Size
ActiveChart.SeriesCollection(series_num).Points(point_num).DataLabel.Tex
= "l"
ActiveChart.SeriesCollection(series_num).Points(point_num).DataLabel.Font.Siz
= 1

end_left
ActiveChart.SeriesCollection(series_num).Points(point_num).DataLabel.Lef

Len(ActiveChart.SeriesCollection(series_num).Points(point_num).DataLabel.Text
/ 2
end_top = ActiveChart.Axes(xlCategory).Top

ActiveChart.SeriesCollection(series_num).Points(point_num).DataLabel.Tex
= txt
ActiveChart.SeriesCollection(series_num).Points(point_num).DataLabel.Font.Siz
= fonsize


If has_label = False The
ActiveChart.SeriesCollection(series_num).Points(point_num).HasDataLabe
= 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 Su
 
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
 
Back
Top