Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
  1. Let me know if you have any other questions or if there's anything else I can assist you with.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,549
Default Add and edit a shape in VBA


Use...
.TextFrame.Characters.Text = strComment
.TextFrame.Characters.Font.Bold = True
.TextFrame.Characters.Font.Size = 18

Also...
A "Comment" is an Object in Excel and should not be used as a variable name.
That doesn't mean it won't always work, but that you are taking the chance that
Excel will always know what you mean/want. I changed it to strComment in my example.

Furthermore...
The use of... Text vs. TextFrame vs. TextEffect will get you again as some point. (I know)
Starting a "code" file and making some notes to yourself can help.
--
Jim Cone
Portland, Oregon USA
(30+ custom ways to sort... http://www.contextures.com/excel-sort-addin.html )




"Hugo Jorgensen" <Hugo
wrote in message ...
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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Edit shape with snap to grid on only allows movement down and righ reb197 Excel Discussion (Misc queries) 5 September 29th 08 08:20 PM
my curser changed from arrow shape to a cross shape???? bj New Users to Excel 1 February 5th 07 03:47 PM
How do I create and/or edit a bell shape graph? Maria E A Charts and Charting in Excel 1 January 4th 07 07:39 PM
Automatique Shape Modeste Excel Discussion (Misc queries) 1 October 18th 05 04:03 PM
Why can't I edit my excel document? Edit buttons shaded. Arl @ CBC New Users to Excel 3 September 7th 05 01:18 AM


All times are GMT +1. The time now is 11:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"