If you take advantage of the built-in styles for Excel 2007 - 2019, setting the AutoShape formatting is Sub AddFormattedTextToShape(oShape As Shape, sText As String) Because it is not backward compatible, I would recommend using the TextFrame object, as shown in the following code. The TextFrame2 member was added in Excel 2007 and gives better control over the formatting of the text. The Shape object has both a TextFrame and TextFrame2 members.
13 add shapes in excel code#
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:įunction AddShapeToRange(ShapeType As MsoAutoShapeType, sAddress As String) As Shape
Of the worksheet, with the Left and Top values increasing to the right and down, respectively. The Left, Top, Width, and Height parameters of AddShape() are specified in points. Not all the AutoShapes are available in the Shapes gallery, so this will also give you a look at some of the hidden ones.
13 add shapes in excel download#
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). You can use them in Excel but they don't have any special properties. Mouse over and click events, but that only applies when they are used in PowerPoint presentations. The online help file states that they support AutoShapeTypes 125-136 are special AutoShapes. The AutoShapeType is a constant that ranges from 1 to 137 for Excel 2003 and earlier versions. The syntax for adding a shape is: (AutoShapeType, Left, Top, Width, Height) Like other collections in VBA, the Shape object is accessed either via its name or index number, as in: ActiveSheet.Shapes("SHAPE_NAME") They are discussed more in the MiscellaneousĮach worksheet contains a Shapes collection consisting of Shape objects. So if the AutoShapeType evaluates to 1, then you also need to check the Type property.Ĭallouts are another special type of shape that can cause confusion. 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. The tricky part comes when the AutoShapeType is 1, which equals the AutoShape constant msoShapeRectangle. If the value is greater than 1, then the shape is one of the types display in the Shapes gallery. For example, if the AutoShapeType value is -2, then for all practical purposes the shape is not an AutoShape. Knowing when to check the Shape.Type property versus the Shape.AutoShapeType is very useful. For AutoShapes, the AutoShapeType property lets you get/set the type of shape as shown in the gallery image below. 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.
Two properties of the Shape object will be used in the code samples below - Shape. Learn more about making flowcharts with FlowBreeze Download
0 kommentar(er)
