#Excel: Exciting New Features – Using a Worksheet UDF to Modify Shapes on a Worksheet by David Hager

 

I recently published the following article about using a worksheet UDF to modify a shape on the worksheet.

https://dhexcel1.wordpress.com/2017/04/19/excel-modifying-shapes-from-an-udf-in-a-worksheet-cell-by-david-hager/

As a brief review, cells B2 and C2 use a data validation list to populate the desired shape and color. Cells G2 and G3 contain the ModifyShape and ModifyShapeColor UDFs.

I have added 2 new features to this powerful technique. The first feature is the ability to change the size of the shape. Entering a value in cells C7 and C8 on the ShapeTest worksheet will change the size of the shape. I have added data validation to those cells to restrict values to the 0.5-2.0 range.

The other feature is the ability to add text to the shape. By entering the text message in cell B13, the new text is added to the shape. The following figure show the layout for the worksheet.

Here is the code for the UDF with the added features.

Function ModifyShape(ShapeNumber, ShapeType, Optional Vis As Boolean = True)

Application.Volatile True

With ActiveSheet.Shapes(ShapeNumber)

.AutoShapeType = ShapeType

.Visible = Vis

.DrawingObject.Characters.Text = Worksheets(“ShapeTest”).Range(“b13”).Value

.Height = .Height * Worksheets(“ShapeTest”).Range(“c7”).Value

.Width = .Width * Worksheets(“ShapeTest”).Range(“c8”).Value

ModifyShape = “done”

End With

End Function

There are a few more features that I plan to add at a future date. Enjoy!

You can download the file here.

ModShape_NewFeatures