CODEWITHSUNDEEP
excelvbashapes
user110084
user110084

Reputation: 279

Excel VBA: How do I change font size and alignment inside text boxes

I have created a grid of text boxes, but I cannot figure out a method to change the font size and alignment (centralise vertically and horizonatally) of the text inside a named textbox.

Sub addtxtbx()
Dim shp As Shape
Dim i As Integer, j As Integer, k As Integer
Dim cindx as long, rindx as long
For i = 1 To 145
Set shp = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, _
            100 + cindx, 100 + rindx, 50, 50)
    cindx = (i - Int((i - 1) / 4) * 4 - 1) * 50 + Int((i - 1) / 48) * 200
    rindx = (Int((i - 1) / 4) - Int((i - 1) / 48) * 12) * 50
    With shp
        .Name = "TxtBx" & (i - 1)
         .Fill.ForeColor.RGB = RGB(204, 102, 255)
    End With
Next i

With ActiveSheet.Shapes.Range(Array("TxtBx11")).TextFrame2.TextRange
    .Characters.Text = "R"
End With
End Sub

Also, I have been scratching my head with the For Next loop's i counter. I used it to rename the text boxes and found that despite using i = 1 to 144, TxtBx144 is not the last box, but the second last, TxtBx2 is the first. So, I tried to get around it by using 1 to 145 and name the box using (i-1) serial which is a bit of a cop out. What did I do wrong that I am failing to see?

Upvotes: 0

Views: 3587

Answers (1)

OpiesDad
OpiesDad

Reputation: 3435

What you want to do is this:

 shp.ShapeRange.TextFrame2.TextRange.Font.Size = 20

where 20 is whatever size you want.

I don't know why your text boxes were getting numbered incorrectly, but your code as written is actually giving me 0-143, not 1-144 so I suspect there is either some code changing i that you haven't put into the question or something else is going on.

Also, the easiest way to figure out how to code stuff like this, is to record a macro, do whatever it is you are trying to accomplish, stop the macro, and then look at the code.

Upvotes: 2

Related Questions