ExcelBanter - Add and edit a shape in VBA

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Add and edit a shape in VBA (https://www.excelbanter.com/excel-discussion-misc-queries/261812-add-edit-shape-vba.html)

Add and edit a shape in VBA

Hi, I have written code to add a rectangle and a line in VBA. When I run the
code it sometimes works fine. If I use F8 to step through the code the code
to add the line works fine but not if I run the code as usual.

The problem with the rectangle is that the text is not inserted into the
rectangle. What is wrong with the code?

Sub Shapes()
Dim Comment As String
Dim Serie_no
Dim Point_no
Dim Left__no
Dim Top_no

Serie_no = 3
Point_no = 3
' Text from a name range
Comment = Range("Comment_txt")
Sheets("Chart").Select
Set myDocument = Sheets("Chart")
' Delete existing shapes
For I = 1 To 20
On Error Resume Next
myDocument.Shapes(I).Delete
Next

'Add a new shape with text
With myDocument.Shapes.AddShape(msoShapeRectangle, _
100, 25, 300, 25) '- left, top width, height
.Name = "Info"
.Fill.ForeColor.RGB = RGB(0, 200, 250)
.Line.DashStyle = msoLineDashDot
.Text = Comment
.Font.Bold = True
.Font.Size = 18
End With
' Check the position of the label to be used as reference for the line
With myDocument.SeriesCollection(Serie_no).Points(Point _no)
.HasDataLabel = False
.HasDataLabel = True
.ApplyDataLabels Type:=xlValue
End With
Left__no =
CInt(myDocument.SeriesCollection(Serie_no).Points( Point_no).DataLabel.Left)
Top_no =
CInt(myDocument.SeriesCollection(Serie_no).Points( Point_no).DataLabel.Top)
Left__no = Left__no + 25
' Add a line
With myDocument.Shapes.AddLine(100, 25, Left__no, Top_no).Line
.DashStyle = msoLineSolid
.ForeColor.RGB = RGB(50, 0, 128)
End With

myDocument.SeriesCollection(Serie_no).Points(Point _no).HasDataLabel =
False
Set myDocument = Nothing
End Sub

Answer: Add and edit a shape in VBA

Hi there! It sounds like you're having some trouble with your
Formula:

VBA

code to add and edit shapes in Excel. Let's take a look at your code and see if we can figure out what's going wrong.

First, let's address the issue with the rectangle not displaying the text. It looks like you're setting the text of the rectangle using the following line of code:

Code:

.Text = Comment

However, it's possible that the
Formula:

Comment

variable is empty or contains a blank space, which would result in the rectangle appearing without any text. To ensure that the text is being set correctly, you can add a
Formula:

Debug.Print

statement to output the value of
Formula:

Comment

to the Immediate window. For example:

Code:

Debug.Print Comment

This will allow you to see the value of
Formula:

Comment

and verify that it's not empty or blank. If it is empty, you'll need to check the source of the
Formula:

Comment_txt

range to make sure it contains the desired text.

As for the issue with the line not appearing when the code is run normally, but appearing when stepping through the code with F8, it's possible that the line is being added too quickly and isn't visible before the code finishes running. To address this, you can add a brief pause using the
Formula:

Application.Wait

method. For example:

Code:

Application.Wait Now + TimeValue("0:00:01")

This will pause the code for one second, allowing the line to be added and become visible before the code continues running.

Let me know if you have any other questions or if there's anything else I can assist you with.

Add and edit a shape in VBA