How to output a text box (Shape) with font (color/size/bold/underline) (VBA)

07/10/202101/29/2023

Are you having trouble retrieving information in a text box?

No wonder you’re in trouble.
Compared to cells, text boxes are much more difficult to handle in VBA.

To solve this problem, I would like to introduce a program that can be easily handled.

In this article, I will show you a program that pastes a string in a text box onto a sheet with a font.

How to output a text box with a font

The program we are going to introduce will write the text in the text box on the sheet, including the font, as shown in the image.

This is an application of the previous article, so if you don’t know what TextFrame2/TextRange is, please read the previous article first.

How to output a text box (Shape) with font (color/size/bold/underline) (VBA) | Y

Are you having trouble retrieving information in a text box? No wonder you're in trouble....

 https://en.yaromai.jp/textbox-string/

Sub OutTextBox()
'Output the contents of the text box on the sheet in the same format

    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim oneShp As Shape
    Dim row_i As Long: row_i = 1
    For Each oneShp In ws.Shapes
        If oneShp.Type = msoTextBox Then 'Process text boxes only
            Call OutText(oneShp, ws.Cells(row_i, 2))
            row_i = row_i + 1
        End If
    Next
End Sub

Private Sub OutText(shp As Shape, outCell As Range)
'Output the contents of the shp object in the same format on the sheet

    'Paste text box name/text
    outCell.Offset(, -1) = shp.Name
    outCell = shp.TextFrame2.TextRange.Text
    
    Dim tmpTR As TextRange2
    Dim wsFont As Font
    Dim fromLeft As Long: fromLeft = 1
    Dim run_i As Long

    'Reflect formatting (text color/size/bold/underline)
    For run_i = 1 To shp.TextFrame2.TextRange.Runs.count
        Set tmpTR = shp.TextFrame2.TextRange.Runs(run_i)
        Set wsFont = outCell.Characters(fromLeft, tmpTR.Length).Font
    
        With tmpTR.Characters.Font
            wsFont.Color = .Fill.ForeColor
            wsFont.Size = .Size
            If .Bold = msoTrue Then wsFont.Bold = True
            If .UnderlineStyle = msoUnderlineSingleLine Then wsFont.Underline = True
        End With
        
        fromLeft = fromLeft + tmpTR.Length
    Next

End Sub

OutText in Private Sub does the main processing.

outCell = shp.TextFrame2.TextRange.Text

In this part, we write out all the text in the text box as before.

After writing out all the text, I reflected the formatting.

The point is the Runs property of TextRange

The key point of this program is the Runs property of TextRange.

TextRange was an object for processing the text of a text box itself, right?

The Runs property is responsible for separating the TextRange into blocks of the same format.

In this case, あい, う, え, and お will be split into blocks.

The return value is a TextRange object, which returns this divided block as a TextRange object.

    For run_i = 1 To shp.TextFrame2.TextRange.Runs.count
        Set tmpTR = shp.TextFrame2.TextRange.Runs(run_i)
        Set wsFont = outCell.Characters(fromLeft, tmpTR.Length).Font

After the For statement, the Runs property is used to Set the TextRange object that has been split.

Then, in the next line, it sets the font of the string in the cell corresponding to the TextRange object that was split.
(If you don’t know the Characters property, please read the official reference.

The rest of the process is just matching the font of the TextRange with the font of the cell string.

The program is a bit difficult to understand, so try stepping through the For loop section and observe!