Excel VBA Code to Center a Shape in a Cell

Quote of the Day

The worst pain a man can suffer is to have insight into much and power over nothing.

Herodotus. This quote reminds me a bit of the curse of Cassandra.


Figure 1: Animated GIF Showing Operation of VBA Shape Centering Macro.

Figure 1: Animated GIF Showing Operation of VBA Shape Centering Macro.

I recently finished a job where the customer wanted an Excel dashboard that displayed metrics for test case completion and various success metrics. This dashboard contained many control shapes that I wanted to be centered in cells. I do not like to manually adjust objects so I googled for a VBA routine that would center a shape. I soon found a nice piece of code by HipGecko on the Mr. Excel forum that centered pictures in the active cell. A simple modification of this code allows it to center shapes, an object type that includes pictures and controls.

Figure 1 shows an animated GIF of the macro centering a flower picture a button shape in two different cells. The code will center the object in the cell if the upper-left-hand corner of the object is in the cell. If multiple shapes are in the cell, the code will center all the shapes on top of one another.

You can download a workbook with the code here. The source is shown below.

 
This entry was posted in Excel. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *