Create default textbox with formatted text in Excel

Question

I got stuck with a pretty simple problem, according to my opinion, however I cannot find any solution for this.

I am trying to create a default textbox (insert -> shapes -> text box) with a certain fill colour (blue, accent 1, lighter 80%) and a certain text (Work Done:[empty paragraph] Findings:[empty paragraph] Conclusion: [empty paragraph]), with the text inside the text box having a red font colour and being bold. I was trying to record a macro while creating this text box however I always get an error message saying: when I run the macro. As I need exactly this text box (without the black text, this is only an example) quite often, it would be great to have a macro for this that I could attach to my customized ribbon.

I figured out that it is quite hard to change formatting things within a text box with VBA. However has still anyone an idea how to accomplish my default text box by using VBA?!

Code:

    Sub Textbox()
'
' Textbox Macro
'
' Keyboard Shortcut: Ctrl+Shift+Y
'
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 59.25, 48.75, 292.5 _
        , 109.5).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorText2
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0.8000000119
        .Transparency = 0
        .Solid
    End With
    DEBUG HERE -> With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 11).Font.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Solid
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(13, 10).Font.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Solid
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(24, 11).Font.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Solid
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
        "Work Done:" & Chr(13) & "" & Chr(13) & "Findings:" & Chr(13) & "" & Chr(13) & "Conclusion:"
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 11).ParagraphFormat. _
        FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 4).Font
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(255, 0, 0)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(5, 7).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(255, 0, 0)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(12, 1).ParagraphFormat. _
        FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(12, 1).Font
        .BaselineOffset = 0
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(13, 10).ParagraphFormat _
        .FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(13, 10).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(255, 0, 0)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(23, 1).ParagraphFormat. _
        FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(23, 1).Font
        .BaselineOffset = 0
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(24, 11).ParagraphFormat _
        .FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(24, 11).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(255, 0, 0)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
    End With
End Sub

You should include the recorded macro code. Also, instead of running the macro you can debug it to find out what is exactly causing the issue. To debug, you can use STEP INTO (usually F8) from within the VBA editor. — Adam Vincent
@AdamVincent: I uploaded my code and inserted a comment where it debugs. However I don't know what causes the problem. — Florian Schramm
Your macro is trying to format text but there is no text in the textbox at that point. — Rory
@Rory: Thanks, you are right. If I take Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _ "Work Done:" & Chr(13) & "" & Chr(13) & "Findings:" & Chr(13) & "" & Chr(13) & "Conclusion:" Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 11).ParagraphFormat. _ FirstLineIndent = 0 and insert it below ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 59.25, 48.75, 292.5 _ , 109.5).Select it works without problems. Thanks again! — Florian Schramm

user3598756 user3598756 · Accepted Answer · 2017-03-23T12:15:14

try this:

   Sub Textbox()
'
' Textbox Macro
'
' Keyboard Shortcut: Ctrl+Shift+Y
'
    With ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 59.25, 48.75, 292.5, 109.5) '<--| add and reference a new shape
        With .Fill '<--| reference referenced shape 'Fill' property
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorText2
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0.8000000119
            .Transparency = 0
            .Solid
        End With
        With .TextFrame2 '<--| reference referenced shape 'TextFrame2' property
            .TextRange.Characters.Text = "Work Done:" & Chr(13) & "" & Chr(13) & "Findings:" & Chr(13) & "" & Chr(13) & "Conclusion:"
            With .TextRange.Characters(1, 11).Font.Fill
                .Visible = msoTrue
                .ForeColor.RGB = RGB(255, 0, 0)
                .Transparency = 0
                .Solid
            End With
            With .TextRange.Characters(13, 10).Font.Fill
                .Visible = msoTrue
                .ForeColor.RGB = RGB(255, 0, 0)
                .Transparency = 0
                .Solid
            End With
            With .TextRange.Characters(24, 11).Font.Fill
                .Visible = msoTrue
                .ForeColor.RGB = RGB(255, 0, 0)
                .Transparency = 0
                .Solid
            End With
            .TextRange.Characters(1, 11).ParagraphFormat.FirstLineIndent = 0
            With .TextRange.Characters(1, 4).Font
                .BaselineOffset = 0
                .Bold = msoTrue
                .NameComplexScript = "+mn-cs"
                .NameFarEast = "+mn-ea"
                .Fill.Visible = msoTrue
                .Fill.ForeColor.RGB = RGB(255, 0, 0)
                .Fill.Transparency = 0
                .Fill.Solid
                .Size = 11
                .Name = "+mn-lt"
            End With
            With .TextRange.Characters(5, 7).Font
                .BaselineOffset = 0
                .Bold = msoTrue
                .NameComplexScript = "+mn-cs"
                .NameFarEast = "+mn-ea"
                .Fill.Visible = msoTrue
                .Fill.ForeColor.RGB = RGB(255, 0, 0)
                .Fill.Transparency = 0
                .Fill.Solid
                .Size = 11
                .Name = "+mn-lt"
            End With
        End With
    End With
End Sub

Create default textbox with formatted text in Excel

2 Answers