Thread: .Select doesn't leave newly created object selected - causes crashes

I'm creating a line, then changing it to an arrow, and then naming it (so I can erase it later when necessary).

The problem is that sometimes (often but not always) the newly created line isn't left selected right after creation. If it's not selected, the next line in the code crashes.

An extract of the code (simplified) follows.

Sub Graphics()

' Much code here

x = 10
x2 = 40
y = 120
y2 = 140

ActiveSheet.Shapes.AddLine(x, y, x2, y2).Select ' <-- Usually doesn't leave the line selected
Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle ' <-- crashes when not selected
Selection.ShapeRange.Item(1).Name = "PGLine1"

' Much more code here

End Sub

This code actually works fine. I've tested it. But the actual Sub, with around 700 lines of VBA, exhibits the problem.

If I manually bypass the problem in debug mode, then the creation/selection of the next drawing object causes the program to crash. (There are maybe 50 such objects.)

Weird, the Graphics Sub worked reliably when it was an independent Sub during the debug phase. But now that it's being called by a much bigger Main program (around 3000 lines of VBA), it crashes most of the time.

I've discovered that if I call the Graphics sub near the beginning of Main, it works fine... but if I call it at the end, it doesn't.

Why doesn't .Select work all the time? How can I fix this?

Borodin

Originally Posted by OnErrorGoto0

Hi,
There is no need to select

With ActiveSheet.Shapes.AddLine(x, y, x2, y2)
.Line.EndArrowheadStyle = msoArrowheadTriangle
.Name = "PGLine1"
End with

should do the same thing.

OK, now reprogramming to eliminate .Select -- but running into some problems:

With ActiveSheet.Shapes.AddShape(msoShapeRectangle, x1, y1, x2, y2)
    .Fill.ForeColor.SchemeColor = ColorFore
    .Fill.BackColor.RGB = RGB(RedReactor, GreenReactor, BlueReactor)
    .Fill.TwoColorGradient msoGradientHorizontal, 4
    .Name = "Rectangle2"
' Add text
    .TextFrame.Characters.Text = "RF"
    .TextFrame.Characters.Font.Size = 8
    .TextFrame.Characters.Text.HorizontalAlignment = xlCenter <-- Crashes!
End With

Could you advise the correct format for centering the text?

Thanks - much appreciated.

How is one to know such things? Where do you guys learn these details?

I have the Walkenbach book on Excel 2003 VBA - he does not mention the word "HorizontalAlignment " anywhere in the book. Same with Latham, Programming in Excel VBA; same with DeMarco, Pro Excel 2007 VBA.

Is there a great VBA reference book around somewhere?