Excel allows you to insert text boxes on a sheet. There are two kinds of text boxes. Form controls and ActiveX controls. Form controls can only be used on worksheets whereas ActiveX controls are far more advanced and can also be used on userforms.

The examples in this post are form controls. Let´s start by creating a text box manually (Excel 2007 and 2010).

  1. Go to tab "Insert" on the ribbon
  2. Click "Text box" button
  3. Drag on sheet
    text box

Did you know that you can add text boxes programatically?

Insert a text box

text box arguments

AddTextbox(Orientation, Left, Top, Width, Height) method

Sub TextBox()
Worksheets("Sheet1").Shapes.AddTextbox(msoTextOrientationHorizontal, 60, 40, 120, 80) _
.TextFrame.Characters.Text = "Text Box"
End Sub

Font and font size

The following code changes font and font size.

Sub ChangeFontSize()
With Worksheets("Sheet1").Shapes("TextBox 1").TextFrame.Characters.Font
.Name = "Arial"
.Size = 20
End With
End Sub

text box 2

Add or change text

This macro changes text to "Text Box 1"

text box 1

Sub ChangeText()
Worksheets("Sheet1").Shapes("TextBox 1").TextFrame.Characters.Text = "Text Box1"
End Sub

Position a shape

position a shape

Sub PositionShape()
With Worksheets("Sheet1").Shapes("TextBox 1")
   .Left = 50
   .Top = 30
End With
End Sub

Read more: Move a shape (vba)

Color a shape

color a shape

Sub ColorShape()
Worksheets("Sheet1").Shapes("TextBox 1").Fill.ForeColor.RGB = RGB(255, 0, 0)
End Sub

Download excel *.xlsm file

Working with text boxes (form controls)v2.xlsm

Just a quick reminder that the awesome Excel Dashboard Course, by Mynda Treacy will be closing on Thursday 30th October, 8pm in Los Angeles, to be exact.