Home > Macros > Basics > Text Box Macro for Worksheet Text Box How to add an ActiveX text box on a worksheet, either manually, or with a macro. Use other macros to format all text boxes, or remove all text boxes, on the active sheet.

Add Text Box on Worksheet - Manual

In an Excel worksheet, you can add text boxes, using tools from the ActiveX Controls toolbox, on the Ribbon's Developer tab.

Advantages of using a Text Box

• The text box can be linked to a worksheet cell, anywhere in the workbook
• Anything that's typed in the text box, is stored in the linked cell.
• Create a big, noticeable data entry spot, without changing column widths, or creating merged cells.

In this screen shot, the text box is beside the linked cell. However you could move the text box, so it covers the linked cell. That would:

• be less distracting to people using the worksheet
• save room on the sheet

Create a Text Box Manually

To manually create the Text Box on the worksheet, follow these steps:

1. On the Excel Ribbon, click the Developer tab
2. Click Insert, then click the Text Box control tool

   

3. On the worksheet, draw a rectangle with the Text Box tool, to create the Text Box.
4. With the Text Box selected on the worksheet, click the Properties command on the Ribbon's Developer tab.
5. In the Properties window, set the properties that you want for the text box, such as Linked Cell.
• NOTE: The address must be typed in the LinkedCell property box. You can't click on the cell to enter its address automatically.
• For linked cells on a different sheet, start with the sheet name, then an apostrophe. If the sheet name has a space character, include single quote marks, e.g.
  • 'Macro Sheet'!$A$2
• 
6. Close the Properties window.
7. On the Ribbon's Developer tab, click the Design Mode setting, to turn it off.
8. In the Text Box, enter text, and it will appear in the linked cell.

   

Add ActiveX Text Box With VBA

Instead of manually adding an ActiveX Text Box to the worksheet, you can add one with programming.

In the following macro, a text box is added on sheet 2, and formatted, and linked to cell A2 on the worksheet.

Sub AddTextBox()
'add textbox - active cell
'linked cell at left, if possible
'or in active cell, if col A
Dim ws As Worksheet
Dim MyCell As Range
Dim oTB As Object
Set ws = ActiveSheet
Set MyCell = ActiveCell
Set oTB = ws.OLEObjects _
  .Add(ClassType:="Forms.TextBox.1")
With oTB
  .Name = "TB" & MyCell.Address
  If MyCell.Column > 1 Then
    .LinkedCell _
    = MyCell.Offset(0, -1).Address
  Else
    .LinkedCell _
    = MyCell.Address
  End If
  .Left = MyCell.Left
  .Top = MyCell.Top
  .Width = MyCell.Width + 5
  .Height = MyCell.Height + 5
  .Object.BackColor _
    = RGB(204, 204, 255)
  .Object.ForeColor _
    = RGB(0, 0, 255)
  .Object.Text = "Hello"
End With
End Sub

Remove All Text Boxes - Macro

If there are ActiveX Text Boxes on the active Excel worksheet, this macro will remove all of them..

The code checks the progID property for each ActiveX object on the sheet. If the progID is Forms.TextBox.1, that object is deleted.

Sub RemoveAllTextBoxes()
'remove all textboxes
' on active sheet
Dim ws As Worksheet
Dim oTB As Object
Dim myID As String
myID = "Forms.TextBox.1"
Set ws = ActiveSheet
For Each oTB In ws.OLEObjects
  If oTB.progID = myID Then
    oTB.Delete
  End If
Next oTB
End Sub

Format All Text Boxes - Macro

These two macros will format all the ActiveX Text Boxes on the active Excel worksheet.

• AllTextBoxesBY16 - changes font to 16 pt, black, with yellow background
• AllTextBoxesBW14 - changes font to 14 pt, black, with yellow background

You can change the settings in the code, for the colours and font size, as needed.

This macro, AllTextBoxesBY16, changes the font size to 16 pt, in black, with a light yellow background.

Sub AllTextBoxesBW14()
'modify all textboxes
' on active sheet
'14pt font, black
'white backgroune
Dim ws As Worksheet
Dim oTB As Object
Dim myID As String
myID = "Forms.TextBox.1"
Set ws = ActiveSheet
For Each oTB In ws.OLEObjects
  With oTB
    .Object.BackColor _
      = RGB(255, 255, 255)
    .Object.ForeColor _
      = RGB(0, 0, 0)
    .Object.Font.Size = 14
    .Height = 20
  End With
Next oTB
End Sub

This macro, AllTextBoxesBY16, changes the font size to 16 pt, in black, with a light yellow background.

Sub AllTextBoxesBY16()
'modify all textboxes
' on active sheet
'14pt font, black
'white backgroune
Dim ws As Worksheet
Dim oTB As Object
Dim myID As String
myID = "Forms.TextBox.1"
Set ws = ActiveSheet
For Each oTB In ws.OLEObjects
  With oTB
    .Object.BackColor _
      = RGB(255, 255, 153)
    .Object.ForeColor _
      = RGB(0, 0, 0)
    .Object.Font.Size = 16
    .Height = 30
  End With
Next oTB
End Sub

Get the Workbook

To try these ActiveX text box macros, download the Worksheet Text Boxes sample file. The zipped Excel file is in xlsm format, and contains the macros from this page. When you open the workbook, enable macros, if you want to test the Text Box macros in the file.

More Tutorials

Create a Listbox

Form Control Buttons

Multi Select Listbox

Move ListBox Items

Move ListBox Items on UserForm