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

user3598756 · Accepted Answer

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

Answers (2)

Related Questions