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!!
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