Macro to insert a preformatted, pre-populated text box [Excel 2010]

Nebulous

New Member
Joined
Oct 7, 2015
Messages
37
Using a macro, I need to insert a text box at the current location in a spreadsheet. The text box and text will have custom formatting and will contain the same formatting and content every time it is generated.

I was going to insert a pic of the text box here but I don't see that I can without posting it to a website and linking to it.

Anyway, the box will be consistent, and this macro will be part of a larger macro that ensures the correct location for placement is selected.

I am no programmer, but I can tweak code.

I tried recording a macro to accomplish this - recorded every step I took to recreate it. I'll paste the code below in case it's useful.

When I run this within my much larger macro, I get "Run-Time error '91': Object variable or With block not set."

Can you help? Is there a better way for me to do this? Thank you!!

Code:
Range("A4").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(2, 8).Range("A1").Select
    ActiveSheet.Shapes.AddLabel(msoTextOrientationHorizontal, 619.5, 551.25, 72, 72 _
        ).Select
    Selection.ShapeRange.Height = 111.6
    Selection.ShapeRange.Width = 408.96
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Solid
    End With
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.0500000007
        .Transparency = 0
        .Solid
    End With
    With Selection.ShapeRange.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
    End With
    With Selection.ShapeRange.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(188, 188, 188)
        .Transparency = 0
    End With
    Selection.ShapeRange.Shadow.Type = msoShadow29
    Selection.Placement = xlMoveAndSize
    Selection.ShapeRange.TextFrame2.WordWrap = msoTrue
    Selection.ShapeRange.TextFrame2.TextRange.Font.Size = 11
    Selection.ShapeRange.TextFrame2.TextRange.Font.Bold = msoTrue
    Selection.ShapeRange.TextFrame2.TextRange.Font.UnderlineStyle = _
        msoUnderlineSingleLine
    
    
        Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
        """""CURRENT STATUS """" FIELD DEFINITIONS" & Chr(13) & "The """"Current Status"""" field provides a high-level view of an ordered item's progression." & Chr(13) & "* Booked: Item has been entered and is being engineered" & Chr(13) & "* Supply Eligible: Procurement planning (material and sources) has begun" & Chr(13) & "* Production Open: Assembly planning has begun" & Chr(13) & "* Awaiting Shipping:  The item is staged for inspection " & _
        "ing" & Chr(13) & "* Closed:  The item has been invoiced"
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 36).ParagraphFormat. _
        FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 36).Font
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoUnderlineSingleLine
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(37, 88).ParagraphFormat _
        .FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(37, 3).Font
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(40, 85).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(125, 56). _
        ParagraphFormat.FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(125, 10).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(135, 46).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(181, 73). _
        ParagraphFormat.FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(181, 18).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(199, 55).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(254, 47). _
        ParagraphFormat.FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(254, 18).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(272, 29).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(301, 68). _
        ParagraphFormat.FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(301, 21).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(322, 47).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(369, 37). _
        ParagraphFormat.FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(369, 10).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(379, 27).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 35).ParagraphFormat. _
        FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 35).Font
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoUnderlineSingleLine
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(36, 88).ParagraphFormat _
        .FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(36, 74).Font
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(110, 14).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(124, 57). _
        ParagraphFormat.FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(124, 2).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(126, 7).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(133, 48).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(181, 73). _
        ParagraphFormat.FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(181, 2).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(183, 16).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(199, 55).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(254, 47). _
        ParagraphFormat.FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(254, 2).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(256, 16).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(272, 29).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(301, 67). _
        ParagraphFormat.FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(301, 20).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(321, 47).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(368, 36). _
        ParagraphFormat.FirstLineIndent = 0
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(368, 9).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(377, 27).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
        .Fill.ForeColor.TintAndShade = 0
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 11
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
    End With
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Nebulous

New Member
Joined
Oct 7, 2015
Messages
37
Actually, I resolved this myself by re-recording the macro and then tweaking the code.
 

Nebulous

New Member
Joined
Oct 7, 2015
Messages
37
SOLVED: Macro to insert a preformatted, pre-populated text box [Excel 2010]

I fixed it by re-recording
 

Watch MrExcel Video

Forum statistics

Threads
1,099,361
Messages
5,468,172
Members
406,569
Latest member
Quest_

This Week's Hot Topics

Top