One of the less than useful new “features” of Excel 2007 is that its macro recorder no longer records operations on shapes, which removes the easiest way to discover the exact names of shape properties, and how to manipulate them. The file ShapeProps.zip contains a spreadheet with a User Defined Function (UDF) that will return a list of property names, and the values for each property, for any named shape. In addition it will generate VBA code to read aditional properties if the correct property names are inserted in a list on the spreadsheet.
The UDF currently reads 94 different properties, which can either be returned as a column array of all 94 values, or if a list of property numbers is entered only the listed property values will be returned. See the screen shot below for examples.
ShapeProp() Output
ShapeProp() code generation sheet
Filed under: Drawing, Excel, UDFs, VBA | Tagged: Drawing, Excel, shapes, UDF, VBA
[...] Drawing in Excel – Manipulating shapes using VBA, Excel 2007 macro recorder no longer records your operations on shapes. That is where Newton Excel Bach’s post on getting shape properties can be handy. Actually the post is a part of series of posts on Drawing in Excel. Read them if you work with shapes often. [...]
[...] Drawing in Excel 7 – Creating drawings from coordinates Posted on November 11, 2008 by dougaj4 Previous Post [...]
Hello
I’m looking for help to solve a problem with an excel sheet.
I need a visual basic routine that make this thing:
i’ve created a sheet where i draw simple form (lines, square) and the using a form i’ll insert the name for that form. I ‘d like to automaitcally add the name that i’ve inserted close to the form.
Do you have any idea on how to do that?
thanks in advance
Vittorio
Hi Vittorio
Could you e-mail a sample and I’ll have a look at it.
dougaj4 at gmail