CODEWITHSUNDEEP
vbaexcel
NeverPhased
NeverPhased

Reputation: 1566

How can I move a shape so its centre lines up with a cell left edge in excel VBA

I have a group of shapes and I can currently move it to the top left edge of a reference cell however I need the middle of the shape to line up with this left edge. There is only "Top" and "Left" parameters and no "Middle" is there any way of doing this?

I could move to left edge then increment by x amount based on the column width but this seems long winded?

Sub Macro4()

Dim x
Set x = Range("A2")

Sheet1.Shapes("Group 9").Left = Sheet1.Cells(13, x).Left
Sheet1.Shapes("Group 9").Top = Sheet1.Cells(13, x).Top


End Sub

UPDATE:

My code is now as follows:

With Sheet1

    .Shapes("Group 9").Top = rng.Top
    .Shapes("Group 9").Left = rng.Left - (.Shapes("Group 9").Width / 2) + (rng.Width / 2)


End With

Which achieves the following

enter image description here

However I need to achieve this:

enter image description here

Whereby the inputed date in red means the shape centre aligns to the right edge of the cell in row 11 which matches the date in this case R11

Upvotes: 1

Views: 2430

Answers (1)

user6028892
user6028892

Reputation:

This will center your shape on the range defined, modify as appropriate.

Dim rng As Range
Set rng = Range("D15")
With Sheet1
    .Shapes("Group 9").Left = rng.Left - (.Shapes("Group 9").Width / 2) + (rng.Width / 2)
    .Shapes("Group 9").Top = rng.Top - (.Shapes("Group 9").Height / 2) + (rng.Height / 2)
End With

Upvotes: 2

Related Questions