Author: Oscar Cronquist Article last updated on May 06, 2018

text box arguments

There are two different 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 text boxes of the group form controls.

The links to the right points to different sections in this article.

There are more VBA articles in the VBA knowledge base.

When to use text boxes?

To create a text box simply go to tab "Insert" on the ribbon and click the "Text box" button.

Now click and hold with left mouse button where you want to place the text box on the worksheet.
text box

Text boxes are great for dynamic worksheets, dashboards or interactive charts. They allow you to build amazing workbooks with minimal effort.

The next section in this article demonstrates how to insert text boxes to your worksheet programmatically.

Back to top

Insert a text box

text box arguments

The AddTextBox method allows you to specify the distance from the cell grids top and left border, see picture above.

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

It also lets you specify the width and height of the textbox you want to create.

The macro below inserts a text box on worksheet Sheet1 60 points from the top border and 40 points from the left border.

The text box width is 120 points and height is 80 points.

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

Back to top

Font and font size

The following code changes the font to Arial and font size to 20 of text box "Textbox 1" located on worksheet Sheet1.

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

text box 2The picture to the right shows text font Arial and font size 20 in text box "Text Box 1" on worksheet Sheet1.

Back to top

Add or edit text

text box 1The macro below changes text in the text box named "Shape1" to "Text Box1" on worksheet Sheet1.

Sub ChangeText()
Worksheets("Sheet1").Shapes("Shape1").TextFrame.Characters.Text _ 
   = "Text Box1"
End Sub

Back to top

Position a text box

position a shapeThe macro below changes the location of text box "TextBox 1" on Sheet1.

50 points from the left border and 30 points from the top border.

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

Read more: Move a shape (vba)

Back to top

Resize a text box

The picture above demonstrates a text box that resizes from height 100 points and width 50 points (left text box) to height 50 points and width 100 points (right text box).

Sub ResizeShape()
With Worksheets("Sheet1").Shapes("TextBox 1")
    .Height = 50
    .Width = 100
End With
End Sub

Back to top

Assign a macro to a text box

The text box allows you to link a macro to a text box, what happens is that when a user clicks on a text box the assigned macro runs.

To assign a macro to a specific text box using VBA see following code.

Sub AssignMacro()
Worksheets("Sheet1").Shapes("TextBox 1").OnAction = "Macro1"
End Sub

The macro above assigns Macro1 to "TextBox 1" on worksheet Sheet1.

Back to top

Change text box color

color a shapeThe picture to the right shows "TextBox 1" with the color red.

The macro below changes the color of "TextBox 1" using RGB values.

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

Back to top

Link text box to cell

This macro links text box "TextBox 1" to cell D2, the text box will now show the value in cell D2 making it dynamic meaning you can easily change what is shown in the text box by changing the value, for example, with a formula in cell D2.

Sub LinkCell()
Worksheets("Sheet1").TextBoxes("TextBox 1").Formula = "=$D$2"
End Sub

Back to top

Hide/Show text box

The visible property allows you to hide the text box.

This macro hides "TextBox 1" on worksheet Sheet1.

Sub HideTextBox()
Worksheets("Sheet1").TextBoxes("TextBox 1").Visible = False
End Sub

Back to top

Delete text box

This macro deletes "TextBox 2" on worksheet Sheet1.

Sub DeleteTextBox()
Worksheets("Sheet1").TextBoxes("TextBox 2").Delete
End Sub

Back to top

Where to copy the code?

Follow these steps to insert macro to your workbook.

  1. Copy macro.
  2. Go to VB Editor (Alt+F11).
  3. Click on "Insert" on the top menu.
  4. Click "Module" to insert a module to the workbook.
  5. Paste macro to the code module.
  6. Exit VB Editor.
  7. Save the workbook as a macro-enabled workbook (*.xlsm).
    If you don't the macro will be gone the next time you open the workbook.

Back to top

Download excel *.xlsm file

Working with text boxes (form controls)v3.xlsm

Back to top