#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.

ModShape_NF1

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

Advertisements

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

  1. Pingback: # Excel Super Links #67 – shared by David Hager | Excel For You

  2. Alex

    Wow, fantastic weblog layout! How lengthy have you been running a blog for? you made running a blog glance easy. The full glance of your site is fantastic, let alone the content material!

    Reply
  3. Alex

    I am now not positive where you are getting your information, but good topic. I must spend some time learning much more or working out more. Thank you for great information I was on the lookout for this information for my mission.

    Reply
  4. Dixie Chandler

    Valuable info. Fortunate me I discovered your site by accident, and I’m stunned why this accident did not took place in advance! I bookmarked it.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s