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
– Adam Vincent, Commented Mar 23, 2017 at 10:46
@AdamVincent: I uploaded my code and inserted a comment where it debugs. However I don't know what causes the problem. — Florian Schramm
– Florian Schramm, Commented Mar 23, 2017 at 11:09
Your macro is trying to format text but there is no text in the textbox at that point. — Rory
– Rory, Commented Mar 23, 2017 at 11:49
@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
– Florian Schramm, Commented Mar 23, 2017 at 14:30

user3598756 · Accepted Answer · 2017-03-23 12:15:14Z

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

Florian Schramm · Accepted Answer · 2017-03-23 14:38:56Z

This is my final code in the end:

Sub Textbox() ' ' Textbox Macro ' ' Keyboard Shortcut: Ctrl+Shift+Y ' ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 59.25, 48.75, 292.5 _ , 109.5).Select 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.Fill .Visible = msoTrue .ForeColor.ObjectThemeColor = msoThemeColorText2 .ForeColor.TintAndShade = 0 .ForeColor.Brightness = 0.8000000119 .Transparency = 0 .Solid End With 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 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

Absolutely you should share you final code, but the proper place for it is by adding a new section to your original question

Collectives™ on Stack Overflow

Create default textbox with formatted text in Excel

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related