Author: Oscar Cronquist Article last updated on June 21, 2020

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, offers a lot more customization, can be manipulated in more ways using VBA, and can also be used on userforms.

The examples in this post are text boxes from the group Form Controls. A text box is an empty field that can be used to submit data from the user or show data to the user. The image above shows a textbox containing text TextBox.

There are more VBA articles in the VBA knowledge base. Text boxes are great for dynamic worksheets, dashboards, or interactive charts. They allow you to build amazing workbooks with minimal effort.

Back to top

Formatting a text box

format a textbox

A textbox can be formatted in many different ways.

  • Opacity
  • background color
  • border width
  • border color
  • font size
  • font color
  • font

The image above shows a textbox containing text "A textbox", a black border and a blue background. Select the text box you want to format.

To select a textbox click on it with the left mouse button. A selected textbox has white circles around it called sizing handles.

I will explain how to use the sizing handles later in this article. Go to tab "Shape Format" on the ribbon. This allows you to change the background color, border size, border color, etc.

format a textbox shape format tab

The "Shape Format" tab is only visible on the ribbon if a textbox is selected.

Back to top

Insert a text box

insert a text box

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

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

Drag with mouse to size the button. Release left mouse button to create the text box.

Back to top

Position, size and align a text box

Move a textbox

Click with left mouse button to select the text box. Move the mouse cursor to the edge of the text box, the cursor changes.

Click and hold with left mouse button on the border of the text box. Drag with mouse to its new location. Release the mouse button to release the text box.

Textbox resize

To resize a textbox you first select the textbox so the sizing handles appear, they are white circles surrounding the textbox.

Click and hold with left mouse button on a sizing handle, then drag with mouse to resize the textbox.

To align a text box to the cell grid, select the text box in order to see the sizing handles. Press and hold the Alt key, press and hold with left mouse button on a sizing handle.

Drag with mouse to align the sizing handle with the cell grid. Release the left mouse button and the Alt key.

Back to top

link text box to cell 1

These steps show how to link a text box to a cell value. The image above shows a text box linked to cell G3, cell G3 has value "Text value". The text box shows the exact same value as in cell G3.

  1. Click the text box to select it.
  2. Click in the formula bar, a prompt appears.
  3. Type the cell reference to a cell you want to link. See the image above.

Link textbox to cell

This makes the text box dynamic meaning if the linked cell value changes the text box cell value changes accordingly. This technique is useful in an interactive dashboard, chart or worksheet.

Back to top

Insert a text box programmatically

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

I will explain where to put this macro later in this article.

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.

'Name macro
Sub ChangeFontSize()

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With Worksheets("Sheet1").Shapes("TextBox 1").TextFrame.Characters.Font

'Change font 
.Name = "Arial"

'Change font size
.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.

'Name macro
Sub ChangeText()

'Change text box text
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.

'Name macro
Sub PositionShape()

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With Worksheets("Sheet1").Shapes("TextBox 1")

   'Move text box 50 points from left worksheet border
   .Left = 50

   ''Move text box 30 points from top worksheet border
   .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).

'Name macro
Sub ResizeShape()

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With Worksheets("Sheet1").Shapes("TextBox 1")

    'Change text box height to 50
    .Height = 50

    'Change text box width to 100
    .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.

'Name macro
Sub AssignMacro()

'Assign macro named Macro1 to text box named Textbox 1
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()

'Change background color
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.

'Name macro
Sub LinkCell()

'Link cell to text box
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.

'Name macro
Sub HideTextBox()

'Hide text box named Textbox 1
Worksheets("Sheet1").TextBoxes("TextBox 1").Visible = False
End Sub

Back to top

Delete text box

This macro deletes "TextBox 2" on worksheet Sheet1.

'Name macro
Sub DeleteTextBox()

'Delete text box named Textbox 2
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

Back to top

Recommended articles