How set align a Paragraph in Shape by VBA Excel?

Question

In a Shape I have 2 paragraphs, where paragraph 1 is right-aligned and paragraph 2 is left-aligned:

If I want to change the alignment of the paragraphs inside the shape, using VBA for Excel, how would I go about that?

Answer 1

It my opinion it is quite simple. Check this code:

Sub AlignParagraphs()

    Dim SHP As Shape
    Set SHP = ActiveSheet.Shapes(1)

    Dim txtRNG2 As TextRange2
    Set txtRNG2 = SHP.TextFrame2.TextRange

    With txtRNG2
        .Paragraphs(1).ParagraphFormat.Alignment = msoAlignRight
        .Paragraphs(2).ParagraphFormat.Alignment = msoAlignLeft
    End With

End Sub

Answer 2

Introduction

First things first: In order to manipulate the text within a shape, you need to work with the TextFrame- or TextFrame2-object, generally using its HorizontalAlignment- and VerticalAlignment-properties. This is described in more detail on e.g. this website, but should be fairly straightforward.

I am guessing that the main problem in your case is that you want to work on different lines in the TextFrame-object. As a first step in trying to figure out to do this, I tried recording what I did when altering the alignment of a single line of text in a textbox, and ended up with this code (after removing all the code which did not apply to the alignment of the text):

With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(9, 14).ParagraphFormat
  .Alignment = msoAlignRight
End With

There are two slight problems I can see with this code:

1. First, it uses TextFrame2 instead of TextFrame, which can cause some problems in older versions of Excel, but I think this is your only option if you want access to the methods needed for what you are trying to do.
2. Secondly, and more importantly, you need to specify what characters to apply the formatting to, and figuring out where the linefeeds are is going to be a bit of a headache, moreso returning those values to the function. It should be doable, though a bit complicated. I wrote a small function returning the position of the nth linefeed, which you can see at the bottom of this post, which may be of some help. A bit clunky though =/ Alternately, if you know (or can figure out) the lengths of the strings on each line of the textbox, e.g. if it the text in the shape is constructed from several smaller strings, that is probably simpler.

Testing sub and function to find nth linefeed

Option Explicit

Sub test()
  Dim i As Long
  i = find_linefeed_no(1, Sheet1.Shapes("TextBox 1").TextFrame2.TextRange.Text)
  Debug.Print i
End Sub

Function find_linefeed_no(lf_no As Long, in_string As String) As Long
  Dim i As Long, at_pos As Long

  If lf_no <= 0 Then
    find_linefeed_no = 1
  Else
    at_pos = 1
    Do While i < lf_no And at_pos > 0
      at_pos = InStr(at_pos, in_string, vbLf, vbTextCompare)
      i = i + 1
    Loop
  End If

  If at_pos = 0 Then
    find_linefeed_no = -1
  Else
    find_linefeed_no = at_pos
  End If

End Function

Note that the function searches for vbLf, as that was the only thing that returned the result I wanted - other options for linefeeds are vbCr, vbCrLf, and vbNewLine. I don't know if this is different in other versions in Excel, but if you don't get it to work, it is at least something to be aware of.

Using the above function to change alignment of a line: The sub below right-aligns the first line in the textbox - to work on the second line instead, increase the line_no-parameters used in the two function calls by one each.

Sub alter_text_alignment()
  Dim start_of_line As Long, end_of_line As Long

  start_of_line = find_linefeed_no(0, Sheet1.Shapes("TextBox 1").TextFrame2.TextRange.Text)
  end_of_line = find_linefeed_no(1, Sheet1.Shapes("TextBox 1").TextFrame2.TextRange.Text)

  If end_of_line = -1 Then
    If start_of_line = -1 Then
      Exit Sub
    Else
      end_of_line = Len(Sheet1.Shapes("TextBox 1").TextFrame2.TextRange.Text)
    End If
  End If

  Sheet1.Shapes("TextBox 1").TextFrame2.TextRange.Characters(start_of_line, end_of_line). _
                             ParagraphFormat.Alignment = msoAlignRight

End Sub

I hope this was of some help, if anything is unclear, please ask and I'll try to clarify.

Answer 3

Try the Link

Sub AlignMultipleShapes()
 'PURPOSE: Align each shape in user's selection (first shape selected stays put)
 'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

  Dim Shp1 As Shape
  Dim Shp2 As Shape
  Dim x As Integer
  Dim y As Integer

  'Count How Many Shapes Are Selected
    x = Windows(1).Selection.ShapeRange.Count

     'Loop Through each selected Shape (align with first selected)
      For y = 1 To x
      If Shp1 Is Nothing Then
         Set Shp1 = Windows(1).Selection.ShapeRange(y)
     Else
        Set Shp2 = Windows(1).Selection.ShapeRange(y)

    'Align Left
      Shp2.Left = Shp1.Left

    'Align Right
      Shp2.Left = Shp1.Left + (Shp1.Width - Shp2.Width)

    'Align Top
      Shp2.Top = Shp1.Top

    'Align Bottom
      Shp2.Top = Shp1.Top + (Shp1.Height - Shp2.Height)

    'Align Middle (Horizontal Center)
      Shp2.Top = Shp1.Top + ((Shp1.Height - Shp2.Height) / 2)

    'Align Center (Vertical Center)
      Shp2.Left = Shp1.Left + ((Shp1.Width - Shp2.Width) / 2)

  End If
 Next y

End Sub

Try this