Text Boxes and Arrows Macro

[liam_conor]

I have a Macro that places a textbox and arrow onto the spread sheet. However it always palces it in cell D15. How do I tell it to place the arrow and textbox into the active cell the user is in.

Sub Identifiers()

ActiveSheet.Shapes.AddLine(161.25, 393.75, 161.25, 447#).Select
Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle
Selection.ShapeRange.Line.EndArrowheadLength = msoArrowheadLengthMedium
Selection.ShapeRange.Line.EndArrowheadWidth = msoArrowheadWidthMedium
Selection.ShapeRange.Flip msoFlipVertical
Selection.ShapeRange.Line.Weight = 2#
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 10
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.Line.BeginArrowheadLength = msoArrowheadLengthMedium
Selection.ShapeRange.Line.BeginArrowheadWidth = msoArrowheadWidthMedium
Selection.ShapeRange.Line.BeginArrowheadStyle = msoArrowheadNone
Selection.ShapeRange.Line.EndArrowheadLength = msoArrowheadLengthMedium
Selection.ShapeRange.Line.EndArrowheadWidth = msoArrowheadWidthMedium
Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle
Selection.ShapeRange.Shadow.Type = msoShadow14
Selection.ShapeRange.Shadow.ForeColor.SchemeColor = 8
Selection.ShapeRange.Shadow.Visible = msoTrue
Selection.ShapeRange.ThreeD.Visible = msoFalse
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 282#, 407.25, _
26.25, 14.25).Select
Selection.Characters.Text = ""
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.ShapeRange.IncrementLeft -135#
Selection.ShapeRange.IncrementTop 28.5
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.Shadow.ForeColor.SchemeColor = 8
Selection.ShapeRange.Shadow.Visible = msoTrue
Selection.ShapeRange.ThreeD.Visible = msoFalse

End Sub

 

[Juan Pablo González]

Hi, try this:

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> Identifiers2()
    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.Shapes.AddLine(ActiveCell.Left + ActiveCell.Width / 2, _
                                    ActiveCell.Top - 53.25, _
                                    ActiveCell.Left + ActiveCell.Width / 2, _
                                    ActiveCell.Top)
        <SPAN style="color:#00007F">With</SPAN> .Line
            .EndArrowheadStyle = msoArrowheadTriangle
            .EndArrowheadLength = msoArrowheadLengthMedium
            .EndArrowheadWidth = msoArrowheadWidthMedium
            .Weight = 2#
            .DashStyle = msoLineSolid
            .Style = msoLineSingle
            .Transparency = 0#
            .Visible = msoTrue
            .ForeColor.SchemeColor = 10
            .BackColor.RGB = RGB(255, 255, 255)
            .BeginArrowheadLength = msoArrowheadLengthMedium
            .BeginArrowheadWidth = msoArrowheadWidthMedium
            .BeginArrowheadStyle = msoArrowheadNone
            .EndArrowheadLength = msoArrowheadLengthMedium
            .EndArrowheadWidth = msoArrowheadWidthMedium
            .EndArrowheadStyle = msoArrowheadTriangle
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">With</SPAN> .Shadow
            .Type = msoShadow14
            .ForeColor.SchemeColor = 8
            .Visible = msoTrue
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        .Flip msoFlipVertical
        .ThreeD.Visible = msoFalse
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    
    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, _
                                       ActiveCell.Left + ActiveCell.Width / 2 - 26.25 / 2, _
                                       ActiveCell.Top, _
                                       26.25, _
                                       14.25)
        .TextFrame.Characters.Text = ""
        <SPAN style="color:#00007F">With</SPAN> .TextFrame.Characters.Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 10
            .Strikethrough = <SPAN style="color:#00007F">False</SPAN>
            .Superscript = <SPAN style="color:#00007F">False</SPAN>
            .Subscript = <SPAN style="color:#00007F">False</SPAN>
            .OutlineFont = <SPAN style="color:#00007F">False</SPAN>
            .Shadow = <SPAN style="color:#00007F">False</SPAN>
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">With</SPAN> .Fill
            .Visible = msoTrue
            .Solid
            .ForeColor.SchemeColor = 10
            .Transparency = 0#
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">With</SPAN> .Line
            .Weight = 0.75
            .DashStyle = msoLineSolid
            .Style = msoLineSingle
            .Transparency = 0#
            .Visible = msoTrue
            .ForeColor.SchemeColor = 64
            .BackColor.RGB = RGB(255, 255, 255)
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        .Shadow.ForeColor.SchemeColor = 8
        .Shadow.Visible = msoTrue
        .ThreeD.Visible = msoFalse
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

I wasn't sure "where" exactly in the Active cell you wanted it... basically because your code didn't put the box in D15 for me !

 

[liam_conor]

COOL...THANKS!