CODEWITHSUNDEEP
excel
Jaron Cook
Jaron Cook

Reputation: 87

Position a shape in Excel based on its center point

Is it possible to position a shape by its center point?

I'm trying to overlay shapes onto a picture of a map in Excel and adjust the size of the shapes based on the value in a cell.

I have the sizing portion figured out, but every time my shapes size increases it slowly starts to move to the right and bottom due to the positioning of its left and top. I would like it if the positioning of my circles would not moved once the data gets refreshed. I only want the size of the shape to be altered.

Any thoughts?

Small Shape

Bigger Shape

Upvotes: 0

Views: 3003

Answers (1)

Byron Wall
Byron Wall

Reputation: 4010

If you know the X/Y (really Left/Top, since {0,0} is upper left) where you want to put it, it is as simple as subtracting off half the size of the shape to get the center to be there.

Here is some simple code which puts the center of a circle at the corner of cell E8. I assume you have some way of selecting the shape based on your question.

Sub PositionByCenter()

    Dim dbl_x As Double
    Dim dbl_y As Double

    'select a cell just to put it somewhere
    dbl_x = Range("E8").Left
    dbl_y = Range("E8").Top

    'grab a reference to a shape
    Dim shp As Shape
    Set shp = Selection.ShapeRange.Item(1)

    'position by the center
    shp.Top = dbl_y - shp.Height / 2
    shp.Left = dbl_x - shp.Width / 2

End Sub

Here is what you get after running that with a circle selected.

circle is now at desired spot

Upvotes: 1

Related Questions