Peltier Tech

Peltier Technical Services - Excel Charts and Programming

Programming Excel 2007 and Excel 2010 AutoShapes with VBA (Guest Post)

by 74 Comments

Tuesday, January 12, 2010 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.
 

Today I am pleased to present a guest post written by my colleague Nicholas Hebb. Nick appears online frequently, commenting on Excel-related blogs and corresponding in the various forums.

Programming Excel 2007 and Excel 2010 AutoShapes with VBA

By Nicholas Hebb

With the release of Office 2007, Microsoft rewrote the drawing tools from the ground up. On the plus side, this meant new shapes, new styles, and the addition of SmartArt. On the downside, they didn’t have time to incorporate AutoShape operations in their macro recorder prior to release. Thankfully, Excel 2010 has added macro support back, but like all macros the code generated is often bloated and relies heavily on the Selection object, which tends to hide the core objects in use. For the most part, the online help is good, but there is some information that doesn’t get explained in detail. This article attempts to provide a basic overview of working with AutoShapes using VBA and touch on some areas that are not covered extensively in the help documentation.

Definitions

Two properties of the Shape object will be used in the code samples below – Shape.Type and Shape.AutoShapeType. Excel has a broad range of shape Types consisting not only of AutoShapes, but also connectors, lines, pictures, charts, comments, and many other graphical items. For AutoShapes, the AutoShapeType property lets you get/set the type of shape as shown in the gallery image below.

Excel 2010 Autoshapes Gallery

Knowing when to check the Shape.Type property versus the Shape.AutoShapeType is very useful. For example, if the AutoShapeType value is -2, then for all practical purposes the shape is not an AutoShape. If the value is greater than 1, then the shape is one of the types display in the Shapes gallery. The tricky part comes when the AutoShapeType is 1, which equals the AutoShape constant msoShapeRectangle. It could be a Rectangle AutoShape, but it could also be anything shaped like a rectangle, such as a text box, a comment, or even a picture. So if the AutoShapeType evaluates to 1, then you also need to check the Type property.

Callouts are another special type of shape that can cause confusion. They are discussed more in the Miscellaneous Issues section below.

Accessing a Shape Object

Each worksheet contains a Shapes collection consisting of Shape objects. Like other collections in VBA, the Shape object is accessed either via its name or index number, as in:

ActiveSheet.Shapes("SHAPE_NAME")

or

ActiveSheet.Shapes(1)

Or, using the For…Each syntax:

Dim shp as Shape
For Each shp in ActiveSheet.Shapes
  MsgBox shp.Name
Next

Adding an AutoShape

The syntax for adding a shape is:

Worksheet.Shapes.AddShape(AutoShapeType, Left, Top, Width, Height)

The AutoShapeType is a constant that ranges from 1 to 137 for Excel 2003 and earlier versions. Excel 2007 added shapes 139 through 183. AutoShapeTypes 125-136 are special AutoShapes. The online help file states that they support mouse over and click events, but that only applies when they are used in PowerPoint presentations. You can use them in Excel but they don’t have any special properties.

To see what the AutoShapeType constant is for each AutoShape, you can copy and paste the following code into the Excel Visual Basic Editor and run it (or download the sample file and run the macro). Not all the AutoShapes are available in the Shapes gallery, so this will also give you a look at some of the hidden ones.

Sub CreateAutoshapes()
  Dim i As Integer
  Dim t As Integer
  Dim shp As Shape

  t = 10
  For i = 1 To 137
    Set shp = ActiveSheet.Shapes.AddShape(i, 100, t, 60, 60)
    shp.TextFrame.Characters.Text = i
    t = t + 70
  Next
  ' skip 138 - not supported
  If CInt(Application.Version) >= 12 Then
    For i = 139 To 183
      Set shp = ActiveSheet.Shapes.AddShape(i, 100, t, 60, 60)
      shp.TextFrame.Characters.Text = i
      t = t + 70
    Next
  End If
End Sub

The Left, Top, Width, and Height parameters of AddShape() are specified in points. The origin is the top left corner of the worksheet, with the Left and Top values increasing to the right and down, respectively. Dealing with points on a worksheet isn’t intuitive, so if you prefer you can add a shape to a given range address by using code like this:

Function AddShapeToRange(ShapeType As MsoAutoShapeType, sAddress As String) As Shape
  With ActiveSheet.Range(sAddress)
    Set AddShapeToRange = ActiveSheet.Shapes.AddShape(ShapeType, _
        .Left, .Top, .Width, .Height)
  End With
End Function

Adding Text to an AutoShape

The Shape object has both a TextFrame and TextFrame2 members. The TextFrame2 member was added in Excel 2007 and gives better control over the formatting of the text. Because it is not backward compatible, I would recommend using the TextFrame object, as shown in the following code.

Sub AddFormattedTextToShape(oShape As Shape, sText As String)
  If Len(sText) > 0 Then
    With oShape.TextFrame
      .Characters.Text = sText
      .Characters.Font.Name = "Garamond"
      .Characters.Font.Size = 12
      .HorizontalAlignment = xlHAlignCenter
      .VerticalAlignment = xlVAlignCenter
    End With
  End If
End Sub

Setting Border and Fill Styles

If you take advantage of the built-in styles for Excel 2007 and Excel 2010, setting the AutoShape formatting is ridiculously easy compared to Excel 2003 and previous versions. Excel 2007 introduced the ShapeStyle property with the 42 preset styles shown below.

The style numbers can be set using a simple line of code:

Shape.ShapeStyle = msoShapeStylePresetXX

Where Shape is the shape object and XX is the style number. The style numbers are shown in the image gallery in order from left to right, top to bottom. For example, the red button in the second row is msoShapeStylePreset10.

Adding Connectors and Lines

Connectors and lines are different objects in Excel. Connectors are special lines  that “connect” to shapes, and if the shape is moved the connector stays connected and reroutes accordingly. Connectors cannot connect to other connectors, but they can connect to the end point of a line.

The syntax for adding a line is straightforward:

Worksheet.Shapes.AddLine(BeginX, BeginY, EndX, EndY)

…with all coordinates as Singles. Adding a connector is a bit more complex, since you typically want it to connect two shapes. The code below calculates the begin and end points, creates the connector, attaches the connector to the two shapes, then finally does a reroute to ensure the shortest path.

Function AddConnectorBetweenShapes(ConnectorType As MsoConnectorType, _
    oBeginShape As Shape, oEndShape As Shape) As Shape

  Const TOP_SIDE As Integer = 1
  Const BOTTOM_SIDE As Integer = 3
  Dim oConnector As Shape
  Dim x1 As Single
  Dim x2 As Single
  Dim y1 As Single
  Dim y2 As Single

  With  oBeginShape
    x1 = .Left + .Width / 2
    y1 = .Top + .Height
  End With
  With oEndShape
    x2 = .Left + .Width / 2
    y2 = .Top
  End With
  If CInt(Application.Version) < 12 Then
    x2 = x2 - x1
    y2 = y2 - y1
  End If

  Set oConnector = ActiveSheet.Shapes.AddConnector(ConnectorType, x1, y1, x2, y2)
  oConnector.ConnectorFormat.BeginConnect oBeginShape, BOTTOM_SIDE
  oConnector.ConnectorFormat.EndConnect oEndShape, TOP_SIDE
  oConnector.RerouteConnections

  Set AddConnectorBetweenShapes = oConnector

  Set oConnector = Nothing 
End Function

Several points worth mentioning are:

  • The ConnectorType can be one of three constants – msoConnectorCurve, msoConnectorElbow, or msoConnectorStraight.
  • The calculations for the beginning and ending points are not normally needed. You could put any values in for the AddConnector() function because once you call BeginConnect and EndConnect, the connector is attached to the shapes and the begin and end points get set automatically.
  • How the end coordinates are specified is not consistent between Excel versions. Prior to Excel 2007, the end coordinates were relative to the begin coordinates. Starting in Excel 2007, the function now uses absolute coordinates.
  • When you route a Connector to an AutoShape, you need to specify the side using a connection site constant. The constants are different for each AutoShape type, but generally they start with the top side = 1 and go counter-clockwise. For example, most rectangular shapes have connection site constants where top = 1, left = 2, bottom = 3, and right = 4.
  • When you call the RerouteConnections() function, it sets the connection sides automatically in order to create the shortest path between the two shapes. So, unless you want a specific routing, you can get usually just guess at the connection site values then call RerouteConnections().

Formatting Connectors and Lines

Like AutoShapes, formatting Connectors and Lines is fairly straightforward in Excel 2007 and 2010. Here is a comparison of two formatting routines for older versions of Excel versus the newer versions:

Sub FormatConnector2003(oConnector As Shape)
  With oConnector
    If .Connector Or .Type = msoLine Then
      ' rough approximation of the Excel 2007 preset line style #17
      .Line.EndArrowheadStyle = msoArrowheadTriangle
      .Line.Weight = 2
      .Line.ForeColor.RGB = RGB(192, 80, 77)
      .Shadow.Type = msoShadow6
      .Shadow.IncrementOffsetX -4.5
      .Shadow.IncrementOffsetY -4.5
      .Shadow.ForeColor.RGB = RGB(192, 192, 192)
      .Shadow.Transparency = 0.5
      .Visible = msoTrue
    End If
  End With
End Sub

Sub FormatConnector2007(oConnector As Shape)
  With oConnector
    If .Connector Or .Type = msoLine Then
      .Line.EndArrowheadStyle = msoArrowheadTriangle
      .ShapeStyle = msoLineStylePreset17
    End If
  End With
End Sub

The Connector property, used above, returns a Boolean indicating whether the shape is a connector. The Type=msoLine statement checks if the shape is a line. In this case the code will format both connectors and lines the same way, but at times you may want handle them separately. (NB: The Insert Shapes gallery of Excel 2007 only lets you add Connectors, not Lines. So unless you are dealing with legacy files or add Lines via code, testing Type=msoLine may never be an issue for you.)

Like the shape styles, you can format the line style by setting the ShapeStyle to one of the msoLineStylePresetXX values, where XX matches the order they appear in the style gallery (below) from left to right, top to bottom.

The Line object has several other members worth mentioning. In addition to the EndArrowheadStyle shown above, there is a corresponding BeginArrowheadStyle property, a DashStyle property, and also a Style property that lets you create double lines.

Miscellaneous Issues

Here are a few Excel 2007 issues with AutoShapes that are good to be aware of :

  • If you copy a set of shapes, older versions of Office gave the option to Paste Special as editable AutoShapes in other Office applications. This option no longer exists in Office 2007. I haven’t tested this in Office 2010 beta yet.
  • In Excel 2007 and the 2010 beta, changing the AutoShape type will disconnect any incoming or outgoing Connectors to a shape.
  • Some print drivers (including PDF export handlers) do not handle printing thick Connectors well, e.g., elbow connectors may straighten. If this happens, either change the line thickness or try grouping all the shapes together prior to printing.
  • The Arc is an AutoShape but needs to be treated as a line when setting the ShapeStyle property.
  • Most of the styles are backward compatible except for styles 37-42 (the glossy button look in the bottom row of the style gallery). Styles 31-36 (the second row from bottom) do not render very well in Excel 2000.
  • You can add Callouts using the AddShape() or AddCallout() methods, but the AddCallout() method will only let you add four types, two of which are actually the same. Callouts have AutoShapeType values in the range of 105-124. Even though they have AutoShapeType values, the Shape.Type property will return msoCallout – not msoAutoShape. Confused? Wait, there’s more. The callouts with AutoShapeTypes from 105-108 actually return a Shape.Type = msoAutoShape – not msoCallout.

Sample File

The sample file includes three demo sheets. The ShapeDemo sheet contains a macro to add two shapes, format them, then add a connector and format it. The Animation sheet has a simple macro showing how to move a shape around the sheet. The CreateAutoShapes sheet has a macro to create all AutoShapes available in your version of Excel.

The ShapeDemo routine has two function calls that are commented out – IlluminateShapeText2003() and IlluminateShapeText2007(). These subs add some gaudy formatting to the first letter of each text block, but they serve to highlight some of the differences between Excel 2007 and previous versions. Two parts of the code worth looking at are the HasText property and the formatting properties of TextFrame2. With the old TextFrame object, you would have to try accessing the TextFrame.Characters.Count property, which throws an error if no text exists. As for the formatting, the font colors in Excel 2003 and previous were limited to the colors in the pallet. In Excel 2007 and 2010, you can add reflections, drop shadows, and glow as well as set the color to whatever RGB value your heart desires.

Lastly, there is a module MCommon containing a sub that deletes all the AutoShapes on a sheet. In order not to delete the command buttons on the sheet (which are shapes too), it creates a ShapeRange of the AutoShapes and deletes that. The online help file shows the syntax for creating a ShapeRange when you know the names of the shapes at design time, but the syntax is a bit tricky when creating one dynamically. The DeleteAllAutoShapes() sub in the sample file shows you how to do this.

Click Here to Download the Sample File

About the Author

FlowBreeze Flowchart SoftwareNicholas Hebb is the founder of BreezeTree Software and author of FlowBreeze Flowchart Software, an add-in that automates flowcharting in Excel.

Posted: Tuesday, January 12th, 2010 under Guest Post.
Tags: , , , .
Comments: 74

Peltier Tech Charts for Excel

Microsoft MVP Logo

Reader Interactions

Comments

  1. Prerna says

    Hi Jon,

    I was reading the above article and downloaded the sample file and have a question here.

    Function AddShapeToRange(ShapeType As MsoAutoShapeType, sAddress As String) As Shape
    With ActiveSheet.Range(sAddress)
    Set AddShapeToRange = ActiveSheet.Shapes.AddShape(ShapeType, _
    .Left, .Top, .Width, .Height)
    End With
    End Function

    -> Here sAddress is usually static like B2:D6. Can this field be dynamic? e.g. I have to create a diagram where Job A triggers Job B, Job C and Job D and then Job B triggers Job E. If I give B2:D6 as a static field and put the code in a loop it gives me an error at sAddress point. Is there anyway to do this?

  3. Prerna says

    Trying to use the below code:

    Set oShape1 = AddShapeToRange(msoShapeFlowchartProcess, “B3:D5”)
    AddFormattedTextToShape oShape1, “Job 1”

    Set oShape2 = AddShapeToRange(msoShapeFlowchartAlternateProcess, “B9:D11”)
    AddFormattedTextToShape oShape2, “Job 2”

    Set oShape3 = AddShapeToRange(msoShapeFlowchartAlternateProcess, “B16:D18”)
    AddFormattedTextToShape oShape3, “Job 3”

    The problem here is we are hardcoding the number of shapes we want which is 3 here and hence the range like B3:D5, B9:D11,B16:D18.

    What I am trying to do is put this in a For loop and repeat it n number of times. So obviously I cannot hard code the ranges and want to give something dynamic.

    For i = 2 To 5
    job_name = Range(“A” & i).Value
    triby_job = Range(“D” & i).Value
    trigjobname_array(i) = Cells(i, “F”).Value
    Set oShape1 = AddShapeToRange(msoShapeFlowchartAlternateProcess, “B3:D5”)
    AddFormattedTextToShape oShape1, Range(“A” & i).Value
    Next
    End Sub
    Private Function AddShapeToRange(ShapeType As MsoAutoShapeType, _
    sAddress As String) As Shape
    With Flow.Range(sAddress)
    Set AddShapeToRange = Flow.Shapes.AddShape(ShapeType, .Left, .Top, .Width, .Height)
    End With
    End Function

    I am getting error at line “With Flow.Range(sAddress)”.
    Run time error 91 object variable or with block not set.

    Can you please suggest something so that the set shape statement can be executed in a loop?

  4. Jon Peltier says

    What’s “Flow”? Is it the sheet where you want the shapes drawn? How does the program understand this?

    In the modified calling sub (below), I construct a range address that includes the worksheet name, and in the function I determine the range, and use the parent of this range (the worksheet that the range is on) as the locaton of the added shape.

    Sub Something()
      Dim i As Long
      Dim job_name As Variant
      Dim triby_job As Variant
       Dim trigjobname_array(2 To 5) As Variant
      Dim job_address As String
      Dim oShape1 As Shape
      Dim wsData As Worksheet
      Dim wsShapes As Worksheet
     
      Set wsData = Worksheets("Data Sheet")
      Set wsShapes = Worksheets("Shapes")
     
      For i = 2 To 5
        job_address = "'" & wsShapes.Name & "'!B" & 3 + (i - 2) * 3 & ":D" & 5 + (i - 2) * 3
        job_name = wsData.Range("A" & i).Value
        triby_job = wsData.Range("D" & i).Value
        trigjobname_array(i) = wsData.Range("F" & i).Value
        Set oShape1 = AddShapeToRange(msoShapeFlowchartAlternateProcess, job_address)
      Next
    End Sub
     
    Private Function AddShapeToRange(ShapeType As MsoAutoShapeType, _
        sAddress As String) As Shape
      With Range(sAddress)
        Set AddShapeToRange = .Parent.Shapes.AddShape(ShapeType, .Left, .Top, .Width, .Height)
      End With
    End Function

  5. Prerna says

    Yes, Flow is the worksheet where I am trying to create a flow diagram in the excel using this macro.

    Table -> the worksheet which has the input in the below format:

    Job name triggered by Job triggered job
    in colmn A in Colmn D in colmn F

    A B
    B A C
    D
    D B E

    above is an example input. Here Job A triggers Job B. Job B triggers C and D. D triggers job E. I am trying to create a Job flow diagram using shapes and I am stuck at the second job where B is triggering 2 jobs C and D where it involves to create 2 branches.

    Appreciate your help on this Jon!!

    Thanks again.

  7. Jon Peltier says

    Trying to program a flow chart generator goes beyond the scope of this post and its comments. Before spending too much time, search Google for ‘excel vba flow chart’, and you’ll see a lot of information from people who have already invented that wheel.

  8. Brett says

    Thank you soo much for this article. I have been searching and searching for some tips on working with drawing objects (because my usual record macro method wasn’t working) and this article explained how to do everything that, so far, I think I need to do to complete my project :)

  10. Jon Peltier says

    Jumpjack –

    The “old fashioned menu” add-in is like many others, which mimic the old menu arrangement as best they can, assuming the old features are still available. The “drawing recorder” is just not available in 2007. It has not just been disabled, it was not (yet) written for the new generation of shapes.

  11. Steve says

    To: Jon Peltier
    Great post. I like how you tackle how to not only create the shapes but also how to use them. I knew how to do it in 2003 but they changed a lot since then.
    Thanks for posting this.

    To: Prerna
    I have created a flow charting tool for excel.
    I can try to help you but you with it.

    I did an automatic flow charting sheet as a result of 3 months work for line item list of instructions. The user had to define where in the flow the instructions would go.
    Mine had not only the ability to create a flow but it also auto arranged it and it would add connecting lines.

    This was very complex and all done in 2003 version of Excel.

    Prior to doing all this I knew nothing about VBA.

    hmm… perhaps I can submit my auto flow thing to Jon and maybe get it put up on the site. Of course Jon or someone would have to update the code and look for mistakes as I am still considered a novice with VBA. :)

  12. sprimax says

    Hi Jon Peltier,

    Would you help me on Excel 2007 VBA code, how to change a foreground of a shape let’s say from grey into blue when a mouse or pointer is over it automatically ?

    Thx so much

    prima – indonesia

  14. prerna says

    Hi Jon,

    I have create a macro in VBA 2003, which draws a diagram in a excel sheet. Is it possible to export this diagram to visio 2003?

    Thanks in advance!
    Prerna

  16. prerna says

    Jon,

    Unfortunately that is not working here. When I do ctrl A and try to copy paste stuff into Visio it does paste the whole diagram.

    Is there any Macro which can be used?

    Prerna

  17. Ibrahim says

    Jon

    I work as a design engineer and use Excel spreadsheet extensively for design and calculations. We have a spreadsheet for each calculation/equipment sizing. What I would like to do is, to generate a CAD style drag and drop object plate. A user can select a graphical representation of a calculation from object plate and drop it to an Excel sheet and link input data to Excel cells and export calculated data to Excel cells.
    In addition, a user can connect/link several objects, so complex calculation scenarios can be performed.
    For example, a pump can be linked to a pipe and a pipe to a valve.

    This is similar to Drawing Shape in Excel. However, I am do not know how to get connectivity of the objects. In another word, I would like to create a VISIO style of drawing object in Excel.
    Using VISIO is not very elegant option based on my experience with a commercial software with integrate VISIO object in Excel.

    I appreciate your help in advance,

    Ibrahim

  19. Jon Peltier says

    Ibrahim –

    Excel does not accommodate Visio type drawings. It might be possible to handle the connectivity programmatically, but there are a lot of details you would have to keep track of. My experience with Visio (not recent) is that Visio is in fact much more elegant than the rest of Office.

  20. Malcolm says

    Hi Jon
    Been trying to create some dial guages and found your shape animation example. I’ve chopped it around a bit and can get a needle (a connector attached to an oval) in my guage to move by using some of the example code to move the oval but haven’t been able to figure which variables control the amount of sweep around a dial and how is screen divided up into positions is like the old machines where the pixels where numbered?

    Sub as per the example
    left_pt = 0.1 * Application.WorksheetFunction.Pi()
    mid_pt = 1 * Application.WorksheetFunction.Pi()
    right_pt = 0.65 * Application.WorksheetFunction.Pi()
    delta = Application.WorksheetFunction.Pi() / 64

    With Worksheets(“Step By Step”).Shapes(“Oval 26”)

    For angle = mid_pt To left_pt Step -1 * delta
    .Left = pivot_x + CSng(r * Cos(angle))
    .Top = pivot_y + CSng(r * Sin(angle))
    Application.Wait Second(Now()) + 1
    Next

  21. Jon Peltier says

    Malcolm –

    A dial gauge is such a poor way to represent a value, showing a single value without any historical context in too large an amount of space. As you have also found, gauges can be very difficult to implement. They might look fancy, but they are not the part of a car’s dashboard that a business dashboard needs to emulate.

    More problems with dials are described in Speedometer Charts and Not Gauges Again!, and some general guidelines and resources for dashboards are presented in Dashboards.

  22. TJ Rucker says

    Hello,

    Super helpful article, thanks. I have been trying to build a Graphical Vessel Calendar with the examples you have provided and have some issues with the shp.TextFrame.Characters.Text command. I have very little exeperience but I am tryign to get this Text box to actually represent soemthign that exists in a Cell on the worksheet. I ran into an article that says you need to use an index but unable to firgure that one out as well.

    Been experimenting with this but does not work = shp.TextFrame.Characters.Text = (A,14)

    Anyhelp is greatly appreciated.

    Thanks
    TJ

  24. Mike Dardis says

    Hi there,

    Awesome article and demo file, it worked great for my coding project. I’ve noticed one (new?) glitch however.

    I’ve got two excel pages, “Data Table” and “Hierarchy”. I can create and delete shapes on the Hierarchy page, and I can create shapes for the Hierarchy page using macros triggered on the Data Table page. However, when I try and run DeleteAllAutoShapes on THESE created shapes, the line at the end, “sr.Delete” fails with message “Run-time error ‘-2147467259 (80004005)’: Method ‘Delete’ of object ‘ShapeRange’ failed”. These shapes become un-deleteable, save by hand (which is annoying for 30 shapes per refresh).

    I changed all of the sheet references in your original code to HierSheet.Shapes etc (HierSheet being a worksheet variable), to eliminate bugs I’ve encountered; I doubt mixed sheet references are the cause (also because many lines of code prior to sr.Delete, such as sr.Select, function fine).

    For a few reasons, I would like to make this work. However, my project does not HINGE on it working.

    Note: The email I provided expires August 25th 2012.

  26. TJ Rucker says

    Hey Jon,

    Thanks for the earlier assistance, worked perfect. We are also trying to forma the text on the object to write to the next line. for example, I have values in two cells on a worksheet that I want to put on both the 1st and 2nd line.

    Set shp = ActiveSheet.Shapes.AddShape(51, 120, 220, 240, i)
    shp.TextFrame.Characters.Text = DataSheet.Cells(r, 2).Value ‘Want this to go to the first line
    shp.TextFrame.Characters.Text = DataSheet.Cells(r, 3).Value ‘ Want this to go to the second line.

    Appreciate the help.
    TJ

  28. eric says

    Hi Nicholas,

    I have very extensive experience in writing vba code, but one glitch in excel 2007-10 puzzles me and I would appreciate your insights

    in xls 2003, when you add a line (manually or using VBA), it’s a line, not a connector
    but in xlsm 2007 -2010, addLine creates a connector with connecting ends, no connector site/node, and it does not seem possible to force the creation of a true line.

    I need the feature because I have numerous applications where i need to attach connector to lines, and you know that you can’t connect 2 connectors to one another.. Using polylines is very cumbersome, because the envelope is a big rectangle as opposed to the nice slim appearance of a simple line.

    The closest solution I found to my need is to draw my line in Excel 2003, where it comes up nice and clean with its 2 extremities being connecting sites/nodes and no connector property, then copy and paste it into Excel 2007-2010 where it retains all its properties and can be used as I wish. I then use VBA to duplicate this element in Excel 2007-20010, and every daughter copy generated like this behaves as I want.

    But it should be possible to generate the line straight from Excel 2007 wouldn’t you think ?

    Any answer, including “Eric you can’t do this anymore” would be appreciated

    Regards

    eric

  29. Jon Peltier says

    Eric –
    Without knowing how you’ve tried to code this, it’s not easy to make any suggestions.
    The Shapes object (collection) has both AddLine and AddConnector methods. Lines.Add has been deprecated, and while VBA will still recognize and run it, you never know what different behavior you’ll see in deprecated code.

  30. Yoh says

    Hi Jon,

    Is it possible to change a font color of a text that’s inside the shape? i’m using excel 2007
    i tried the following code, but it doesnt work

    Selection.ShapeRange.TextFrame.TextRange.Characters(2, 3).Font.Color = 11851260

    Regards
    Yoh
    it came with an error saying “Object doesnt support this property or method”

  31. Jon Peltier says

    Excel 2007 won’t record any formatting steps in charts or shapes, but in 2010 I recorded enough code to get this syntax:

    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(2, 3).Font.Fill.ForeColor.RGB = 11851260

    But that seems like a long hierarchy of objects. I fiddled a bit and found this to work:

    Selection.ShapeRange(1).TextFrame.Characters(2, 3).Font.Color = 11851260

    Apparently TextFrame2 has a TextRange, but TextFrame does not.

  33. Fredrik Nelson says

    Hi, I have a problem that maybe with your knowledge you can share some light.
    ive tried powerpoint,Word and Excel but all gives me simimlar errors
    Using a organisation like smartart i can for example change the TextFrame2, the Rotation etc
    ActiveDocument.Shapes(1).SmartArt.Nodes(1).Shapes.Item(1).Rotation=35
    this will turn one of the squares. but i f i try ActiveDocument.Shapes(1).SmartArt.Nodes(1).Shapes.Item(1).Height = 33
    i always get an error, all documentations seems to indicate that height and width are read/writes but it does not work. Office 2010. Any clues to whats going on, would be very nice. (sorry for posting such question here but your blog seems to be update. Thanks in adavance.

  35. Bill Benson says

    Hi Jon, good to see your articles are still making the Excel working world go around. I have a question related to chart/shape formatting which someone recently asked in another forum and indeed it is a little puzzling. The font choices for shapes are slicker than those for normal cells. So this party was asking if there were a way to get the same font dialog choices that Ctrl-Shift-P offered for cells, as one might see when doing so for shapes. I hunted around in the object model for dialog constants which contain the word “font”, I found the below, none of which seem to be the one that fires up when trying to alter the fonts of a shape:

    xlDialogActiveCellFont, xlDialogFont, xlDialogFontProperties, xlDialogFormatFont, xlDialogReplaceFont,
    xlDialogStandardFont, xlDialogWebOptionsFonts

    Any ideas where to look for the object (ie, is it callable from VBA for use in other situations than for shapes)?

  37. Sébastien Daviet says

    Hi Jon
    I was wondering how I can disable Right Click on shapes. First of all, is it possible with Excel 2010 ?
    Regards
    Sébastien

  39. GSM says

    Hi Jon,
    Using VBA to change the properties of a toggle button with the name of “Abcde” in Excel 2007 I got e.g. this example to work for changing the width:
    ActiveSheet.Shapes(“Abcde”).Width = 100
    I cannot get the coding right though to change the “Value” property of the button from “True” to “False” or vice versa with eg.
    ActiveSheet.Shapes(“Abcde”).Value = False (to indicate that the button is on or off)
    What is the correct code? I would appreciate your help tremendously.
    Regards

  40. Jon Peltier says

    The shape doesn’t have any value. But the shape is the container for the toggle button, which is an OLE object. The syntax is a bit obscure, but here’s how you change its value to True:

    activesheet.shapes(“abcde”).oleformat.object.object.value = true

  43. K.S.Narasimhan says

    It is a Great Blog. I want to develop some shapes which are used in Bar Bending normally of Lines and angles.I am not able to record the macro in Excel2007. If it is not inconvenient Could you please guide me with the code for a straight line for 2007

  44. Jon Peltier says

    KSN –

    In brief, the answer is in the section “Adding an AutoShape”:
    Worksheet.Shapes.AddShape(AutoShapeType, Left, Top, Width, Height)
    Use the Object Browser to find the AutoShapeType, which for a line is msoConnectorStraight (I don’t know why it isn’t msoLineStraight, because there is a whole set of Connector shapes, but this is how Microsoft works).
    Excel 2007 has a macro recorder, but it doesn’t work with charts and shapes. The macro recorder in earlier and later versions of Excel do work with charts and shapes. You should investigate upgrading (or if all else fails, downgrading) Excel.
    In the future, you should follow the advice in this article: Getting Answers For Your Excel Questions.

  47. traveller says

    If a textFrame has been autosized, is it possible to determine the cell row beneath a textFrame2 shape?
    This will allow me to scroll the depth of a textFrame.

  49. Iboro says

    Mr. Peltier sir, I must thank God for you. No, really, your tutorial on how to work with shapes in excel is amazing. Thanks a lot and remain blessed.

  50. Neil de Villiers says

    Hi Jon.

    Please assist if possible. I’m writing a Cutting List program in Excel 2010. It takes sizes from exce cellsl then creates the shapes based on that. The problem is that creating the shapes in VBA code, creates the shapes all on top of 1 another, (I generate between 4 to 200 shapes per code run).

    So here comes the issue: Selecting all shapes and then trying to distribute it (Hor) or (Ver) keeps the shapes connected in some way because my shapes, although distributing, still overlaps 1 another.

    Is there a way in vba to distribute shapes to sit side by side, ie connect the left side of 1 shape to the right side of the next shape?

    Your assistance would be highly appreciated, as I have been spending hours Googling this with no success.

  51. Neil de Villiers says

    Sub LeftAlignAllObjects()
    Dim i As Integer
    Solved thx to http://www.tushar-mehta.com/excel/vba/vba-embedded%20or%20linked%20objects.htm Example 3.

    Cade that allows this is posted here.

    With ActiveSheet.Shapes
    For i = 2 To .Count
    .Item(i).Left = .Item(1).Left
    .Item(i).Top = .Item(i – 1).Top + .Item(i – 1).Height
    Next i
    End With
    End Sub

    Thanking you for your blog. It seems that the error is a universal problem and only way to solve it is manually moving the Shapes or using the above code.

  52. M. Klein says

    Hi Jon,

    how can I draw multiple shapes in a row, without them overlaping and without defining a fixed point for all of them? I want to draw the first shape with a fixed reference and draw the next shapes in a sequence one after the other one. Is this possible?
    Thanks!

  54. Mon says

    Hello Jon,

    I am writing a macro in which for every row in TableX an Autoshape is inserted in another worksheet, additionally I would like to link the information in column M (each row a new shape) to the last inserted autoshape.
    Is it possible to do this, by using this:
    shp.TextFrame.Characters.Text = SomeWorksheet.Range(A, 14).Value

    If so, how can I make the row reference variable? I was thinking about a sort of loop in which a counter sets the value of the row. But I cant figure out how to make it work. Or are there any other easier alternatives?

    Thank you.

  55. Ron MVP says

    I am trying to use .AddShape in this macro. It works in Excel 2016, but I keep getting a syntax error in 2010

    Sub Fill2Colors()
    ' based on code by Héctor Miguel
    ' Works in 2016, does not work in 2010 ...
    Dim sL As Single
    Dim sT As Single
    Dim sW As Single
    Dim sH As Single
    Dim sN As String
    Const vbGrey = &H808080
    With Range("C30:Y30")
        sL = .Left
        sT = .Top
        sW = .Width
        sH = .Height
        sN = .Address(False, False)
        With ActiveSheet.Shapes
            On Error Resume Next
            .Range(Array(sN & "1", sN & "2", "grp" & sN)).Delete
            On Error GoTo 0
            With .AddShape(msoShapeRectangle, sL, sT, sW, sH / 2)
                .Name = sN & "1"
                .Line.Visible = msoFalse
                .Fill.Visible = msoTrue
                .Fill.Transparency = 0.75
                .Fill.ForeColor.RGB = vbBlue
            End With
            With .AddShape(msoShapeRectangle, sL, sT + sH / 2, sW, sH / 2)
                .Name = sN & "2"
                .Line.Visible = msoFalse
                .Fill.Visible = msoTrue
                .Fill.Transparency = 0.75
                .Fill.ForeColor.RGB = vbGrey
            End With
            With .Range(Array(sN & "1", sN & "2")).Group
                .Name = "Grp" & sN
                .Placement = xlMoveAndSize
            End With
        End With
    End With
End Sub

    On this line

    With .AddShape(msoShapeRectangle, sL, sT, sW, sH / 2)

    I get a “run-time error ‘1004’: Application-defined or object-defined error”.

    But I don’t see anything wrong with it.

    This is on Win 10 and I have both Office 2016 Pro Plus (legal license) and 2010 Home and Student. I wonder if this dual install is somehow causing this issue.

  56. pete says

    I have a question that seems un-answered:

    I have a shape.
    say a rectangle AutoShape: Rounded rectangle 1
    Its text it set say a formula “=sheet1!$a$”
    A1 has a value of “hello”,
    So does the Rectangle

    Now I was the change the formula to B1 which says goodbye

    shapes(“Rounded rectangle”).Formula does not work. But if I select the shape, then selection.Formula = B1 does….
    so why question is in the expression
    shapes(name).???.formula

    what is ???

  57. Jon Peltier says

    Hi Pete –

    This is an inconsistency in Excel’s object model. I don’t think you can access the .Formula property of a shape object. If you record a macro, it’s

    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Formula = "=Sheet1!$B$1"

    but if you play it back, you get an error. The only way to access the formula is by selecting the shape first, and getting the .Formula property of the Selection object.

  58. Jon Peltier says

    Ron –

    I ran this abbreviated bit of your code, and had no such error. Windows 10, Office 2016 (with 2013, 2010, 2007, and 2003 also installed on this machine).

    Sub MyMacro()
  Dim sL As Double, sT As Double, sW As Double, sH As Double, sN As String
  sL = 150: sT = 100: sW = 250: sH = 150
  sN = "MyName"
  With ActiveSheet.Shapes
    With .AddShape(msoShapeRectangle, sL, sT, sW, sH / 2)
      .Name = sN & "1"
    End With
  End With
End Sub

  59. Jon Peltier says

    Pete –

    Here is a summary of VBA code that can access the formula of the text in a shape: 

      ' macro recorded, doesn't work
  ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Formula = "=Sheet1!$B$1"
  
  ' seems like it should work, doesn't
  ActiveSheet.Shapes("Rounded Rectangle 1").Formula = "=Sheet1!$B$1"
  
  ' deprecated, but works like a charm
  ActiveSheet.DrawingObjects("Rounded Rectangle 1").Formula = "=Sheet1!$B$1"
  
  ' obscure, but works
  ActiveSheet.Shapes("Rounded Rectangle 1").OLEFormat.Object.Formula = "=Sheet1!$B$1"
  
  ' also obscure, also works
  ActiveSheet.Shapes("Rounded Rectangle 1").DrawingObject.Formula = "=Sheet1!$B$1"
  
  ' awkward, works but requires selection of the shape
  ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Select
  Selection.Formula = "=Sheet1!$B$1"

  60. Sonny Day says

    I have created a line on a spreadsheet in Excel 2010, with the vba command AddLine(Beginx, Beginy..). Now I want to change the position of this line, without deleting it and drawing another line with all of the same properties. Is this possible? Can I access [beginx, beginy…] and change them?

  61. Jon Peltier says

    Sonny –

    You can’t use VBA to get BeginX, BeginY, EndX, and Endy from the line, all you can get is the Left, Top, Width, and Height properties of the rectangle whose diagonal corners are defined by the line. You also can’t use VBA to tell whether the line follows the upward or downward sloping diagonal of the bounding rectangle, nor can you tell which end of the line is the beginning and which is the end. To move and resize the line, you can use IncrementLeft, IncrementTop, ScaleWidth, and ScaleHeight (which I don’t like doing), or you can provide new Left, Top, Width, and Height properties.

    The easiest way to get another line with the same properties is to make a copy of the first, but again, VBA can’t tell you its orientation, if you’re trying to point the line at something. So if you want to ensure that your line has the intended orientation, you should figure out the BeginX, BeginY, EndX, and Endy that you need, then pick up and apply formats just like using the Format Painter:

      ActiveSheet.Shapes(1).PickUp
  ActiveSheet.Shapes(2).Apply

  63. Jerry Hayden says

    We used an Excel 2003 file to generate flow charts from robot program text files. Now that we have upgraded to Excel 2010 the program no longer runs. It stops on lines of code that reference msoShapeRectangle. An example is “Unable to get the Characters property of the Rectangle class”.
    Is the ShapeRectangle properties different in the newer versions? I have been unable to view a list of properties for ShapeRectangle, any idea where I could view them and see what’s different?
    Thanks,
    Jerry

  64. Jon Peltier says

    Jerry –

    Some objects have been extended, and some properties have been deprecated; in addition, a few objects and properties behave differently in “Classic” (up to 2003) and “New” (2007 and later) Excel.

    If the properties are deprecated but still available, you can find them in the Object Browser if you right click on one of the panes and select Show Hidden Members.

    If old code that used to work now fails, you need to find alternatives. Also, alternatives may be beneficial for old code that still works, as newer properties may be more useful.

    Post samples of code leading up to the errors, and I may be able to help.

  65. Ron says

    run the macro in debug mode to find the line it abends on. Look through the commands on that line, then take a look at these links to see if you can find newer versions of the problem commands

    https://support.office.com/en-us/article/Excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188
    Applies To: Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 Excel Online Excel for iPad Excel for iPhone Excel for Android tablets Excel Starter 2010 Excel Mobile Excel for Android phones

    Click a letter to go to functions that start with it. Or press Ctrl+F to find a function by typing the first few letters or a descriptive word. To get detailed information about a function, click its name in the first column.

    https://support.office.com/en-us/article/Excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb
    Applies To: Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 Excel Online Excel for iPad Excel for iPhone Excel for Android tablets Excel Starter 2010 Excel Mobile Excel for Android phones

    Worksheet functions are categorized by their functionality. Click a category to browse its functions. Or press Ctrl+F to find a function by typing the first few letters or a descriptive word. To get detailed information about a function, click its name in the first column.

  67. Jerry Hayden says

    Jon,
    I believe you’re onto the problem. Here is the code that fails.

    write3:
    With Selection.Characters(Start:=come1L + 2, Length:=come2L).Font ‘ Error on this line
    .Name = Language
    .Size = 8
    End With

    I was not showing Hidden Members and doing so now shows me ( I think) that Characters and Font are legal members.

    What am I missing?

    Thanks,
    Jerry

  68. Jerry Hayden says

    Made some progress.
    On the error line after Start and Length the original code used a “:=” combination.
    I removed the “:” and only left the “=” and it runs by it now.

    Of course it stops again later but let me see what I can do with it myself.

    Thank you much, the trick for me was to show hidden members.

    Jerry

  69. Jon Peltier says

    Jerry –

    The code should run whether or not you show the hidden members. And using = instead of := should cause an error.

    When I get stuck with obscure syntax, I used to go to the help files, but those no longer have useful examples. Instead, I turn on the Macro Recorder while I manually do something similar to what I want my code to do. The recorded code is usually pretty ugly, but I get things like:

        With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 2).Font
      .NameComplexScript = "+mn-cs"
      .NameFarEast = "+mn-ea"
      .Fill.Visible = msoTrue
      .Fill.ForeColor.ObjectThemeColor = msoThemeColorLight1
      .Fill.ForeColor.TintAndShade = 0
      .Fill.ForeColor.Brightness = 0
      .Fill.Transparency = 0
      .Fill.Solid
      .Size = 11
      .Name = "+mn-lt"
    End With

    That’s a lot of mumbo-jumbo, with a lot of meaningless default formatting, but I would use it as a guideline to change your little bit of code to something like this, depending on how you reference the shape:

        With ActiveSheet.Shapes("Rectangle 1").TextFrame2.TextRange.Characters(Start:=come1L + 2, Length:=come2L).Font
        .Name = Language
        .Size = 8
    End With

Trackbacks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Peltier Tech Charts for Excel