PDA

View Full Version : Macro for adding arrows and textboxes to a graph



rzaa
03-21-2006, 05:20 AM
Hi

I created a graph showing sales of different products in 2005 to 2006.

The graph got really popular but it is unfortuneately time consuming to create. Therefore i made a very simple macro for inserting 12 arrows and 12 textboxes (theres usually 12 products)

Its still rather time consuming because i still have to place the text boxes above the arrows and then place both above their individual graphs (data points)

Is it possible to create a macro that automatically obtains info about the height of the graphs(data points) and places the arrows and textboxes in accordance?

The macro I now use is really simple, I just repeat the following code 12 times and enter different coordinates for both arrows and textboxes.

I cant figure out how and if this can be done in a macro and I will appreciate any help at all.

Thanks in advance

-Rzaa




Sub Makro6()
ActiveSheet.Shapes.AddLine(500.25, 237#, 518.25, 252.75).Select
Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle
Selection.ShapeRange.Line.EndArrowheadLength = msoArrowheadLengthMedium
Selection.ShapeRange.Line.EndArrowheadWidth = msoArrowheadWidthMedium
Selection.ShapeRange.Flip msoFlipVertical
ActiveSheet.Shapes.AddLabel(msoTextOrientationHorizontal, 501#, 219.75, 0# _
, 0#).Select
Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue
Selection.Characters.Text = "%"
With Selection.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Fed"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("O25").Select
End Sub