CODEWITHSUNDEEP
excelvba
apang
apang

Reputation: 103

Change font size in textboxes

I have pasted textboxes over charts.

I'm trying to change the font size within that textbox for all charts.

This is a picture of what I mean
enter image description here

Sub shapeFont()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
    With shp.ShapeRange.TextFrame2.TextRange.Font
    .Size = 30
    End With
Next shp

End Sub

Upvotes: 1

Views: 1959

Answers (2)

JvdV
JvdV

Reputation: 75960

These textboxes seems to be embedded into ChartObjects. So you have to loop your ChartObjects instead. Then loop over its Shapes collection and only when you encounter a proper TextBox you should change it's font. Embedded textboxes can be inserted into a chart by selecting a chart and inserting a shape. From then, these shapes move with the chart.

For example:

Sub FindTextBoxes1()

    Dim c As ChartObject
    Dim s As Shape

    For Each c In ActiveSheet.ChartObjects
        For Each s In c.Chart.Shapes
            If s.Type = msoTextBox Then
                s.TextFrame2.TextRange.Font.Size = 30
            End If
        Next s
    Next c

End Sub

Specify a Workbook and Worksheet variable for pinpointing better where you want this macro to operate (instead of an ugly ActiveSheet)

Inspiration from here

Whereas embedded textboxes are great to distinguish shapes you do want to change from the ones you don't want to change, you also don't need to actually select your textboxes first to be able to change your Font.Size in case you want to iterate over all non-embedded textboxes. Simply refer to the Characters within the TextRange. For example:

Sub FindTextBoxes2()

    Dim s As Shape

    For Each s In ActiveSheet.Shapes
        If s.Type = msoTextBox Then
             s.TextFrame2.TextRange.Characters.Font.Size = 30
        End If
    Next

End Sub

Upvotes: 3

Anna Semjén
Anna Semjén

Reputation: 787

VBA is a strange language if you actually select it it is able to do it.. 

Sub shapeFont()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
    shp.Select
    With Selection.ShapeRange.TextFrame2.TextRange.Font
    .Size = 30
    End With
Next shp

End Sub

If chart object is present and you have separate textboxes this works:

Sub shapeFont()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
    shp.Select
    Debug.Print (shp.Type)
    If shp.Type = msoTextBox Then
        With Selection.ShapeRange.TextFrame2.TextRange.Font
       .Size = 50
    End With
   End If
Next shp
End Sub

If you want to update the boxes on the chart the code provided by https://stackoverflow.com/users/9758194/jvdv will work

Upvotes: 2

Related Questions