Home/ Questions/Q 11415
Next
Answered
computing
  • 9
Asked: 2021-12-08T03:39:46+00:00 In:

Solved Help To Center An Object In a Specific Cell Using Excel VBA

  • 9

I need help to modify the below code to horizontally center the object (down arrow) in the specified cell (“C3”), so it is centered in column C, rather than left aligned.
I think there may be a way to do this by using an offset to the next column and then dividing by 2, to result in a centered position, but am not sure how to do so. I also need to add this same object to the adjacent cell, (“D3”), which will also need to be horizontally centered within column D. Any help would be much appreciated!

The below code inserts the object that I want and positions it within the correct cell, but has it left aligned, vs centered in that column. The width of all columns in the worksheet may change, so I can’t specify a fixed point position.

 

Sub AddDownArrow()

Dim CelLeft As Double
Dim CelTop As Double
Dim shp As Shape
Dim Cel As Range

Set Cel = Range("C3")
CelLeft = Cel.Left
CelTop = Cel.Top

Set shp = ActiveSheet.Shapes.AddShape(msoShapeDownArrow, CelLeft, CelTop, 38.16, 77.04)

End Sub

 

message edited by User444

Share

    1 Answer

    1. Best Answer
      First, a posting tip:

      Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Thanks!

      As for your question(s), try this:

      Sub AddDownArrow_v2()
Dim shp As Shape
Dim Cel As Range
'Loop through Columns 3 & 4
  For myCol = 3 To 4
   Set Cel = Cells(3, myCol)
'Insert and Center Shape
      With Cel
        Set shp = ActiveSheet.Shapes.AddShape _
                 (msoShapeDownArrow, .Left, .Top, 38.16, 77.04)
            shp.Left = .Left + ((.Width - shp.Width) / 2)
      End With
  Next
End Sub

      Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

      message edited by DerbyDad03

      • 0