It is well-known that an used-defined function created with VBA and entered as a worksheet formula cannot alter the worksheet. But, it is not true. A number of examples where this can occur have been discussed and documented. In particular, a post by John Walkenbach referred to the altering of worksheet shapes (see link below) with an UDF.
http://dailydoseofexcel.com/archives/2007/01/12/modifying-shapes-and-charts-with-udfs/
Since this technique has not gained popularity in use, few people know about it. So, I have made an user-friendly model to demonstrate its utility.
The biggest deficiency in working with shape properties is the lack of a good source of lookup information on what the index numbers refer to. I iterated (manually) through all 181 index numbers for shape type (assigned the shapes names based on what I decided, they can be changed is desired), and the table I created can be seen in the ShapeLists worksheet in the example workbook. I defined a list of shape names and used it in a Data Validation List for cell B2. Then, in cell B3, this formula looks up the correct shape index number for the selected shape. That result is used by the ModifyShape UDF in cell G2.
=INDEX(ShapeIndex,MATCH($B$2,ShapeName,0)) ‘in B3
=ModifyShape(1,$B$3) ‘in G2
Here is the ModifyShape VBA function.
Function ModifyShape(ShapeNumber, ShapeType, Optional Vis As Boolean = True)
With ActiveSheet.Shapes(ShapeNumber)
.AutoShapeType = ShapeType
.Visible = Vis
End With
End Function
Note that it has an optional 3rd argument. It is TRUE by default, but if is set to FALSE, the shape is invisible. And, all of this occurs simply by the recalculation of the UDF.
There are a number of shape properties that can be manipuated by an UDF. One fuction that I specifically designed for this article is the ModifyShapeColor function.
Function ModifyShapeColor(ShapeNumber, rColor, gColor, bColor)
With ActiveSheet.Shapes(ShapeNumber)
.Fill.ForeColor.RGB = RGB(rColor, gColor, bColor)
End With
End Function
But, for this example to be fully effective, I needed a reference table of RGB colors that could be utilized by this function. I was able to find such a table at this source:
http://rapidtables.com/web/color/RGB_Color.htm
and I pulled this information into the workbook by using Power Query. Then, I copy/pasted it to the ShapeLists sheet and deleted the query (no need to refresh static information). I defined a list of shape colors and names and used the Shape color name in a Data Validation List for cell C2. Then, in cells C3:C5, these formulas look up the correct RGB numbers for the selected color.
=INDEX(Red,MATCH($C$2,ColorName,0)) ‘in cell C3 etc.
=ModifyShapeColor(1,$C$3,$C$4,$C$5) ‘in cell G3.
You can see the resulting shape and color in the following figure.
Now, realize that the worksheets can have multiple shapes controlled by the UDFs pointing at each shape. This opens up a new way to visualize data. I hope that you can incorporate this technique into your projects.
You can download the workbook here:
Thank you
Pingback: #Excel Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You
Pingback: #Excel Super Links #17 – shared by David Hager | Excel For You
Pingback: #Excel Worksheet UDF that Adds a Comment to Any Cell by David Hager | Excel For You
Pingback: #Excel: Exciting New Features – Using a Worksheet UDF to Modify Shapes on a Worksheet by David Hager | Excel For You
Pingback: #Excel Super Links #90 (Special Edition) – shared by David Hager | Excel For You
Pingback: #Excel Super Links #150 – Special Edition | Excel For You
Pingback: #Excel: Generate a List of Antonyms Using an User-Defined Function (UDF) | Excel For You