Create VBA Text Box on Worksheet

How to add an ActiveX text box on a worksheet, either manually, or with a macro

Create an ActiveX Text Box on the Worksheet

In an Excel worksheet, you can add text boxes from the ActiveX Controls toolbox on the Ribbon's Developer tab. The text box can be linked to a worksheet cell.

text box can be linked to a worksheet cell

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

    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.

    set the properties

  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.

    enter text in the text box

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()
Dim ws As Worksheet
Dim oTB As Object
Set ws = Worksheets("Sheet2")
   Set oTB = ws.OLEObjects.Add(ClassType:="Forms.TextBox.1")
    With oTB
    .Name = "MyTB"
    .LinkedCell = "$A$2"
    .Left = ws.Range("B2").Left
    .Top = ws.Range("B2").Left
    .Width = ws.Range("B2").Width
    .Height = ws.Range("B2").Height
    .Object.BackColor = RGB(204, 204, 255)
    .Object.ForeColor = RGB(0, 0, 255)
    .Object.Text = "Hello"
    End With

End Sub

More Tutorials

Create a Listbox

Multi Select Listbox

Move ListBox Items

Move ListBox Items on UserForm

go to top

Don't Miss Our Excel Tips

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.

Get weekly Excel tips from Debra



Search Contextures Sites

Search Contextures Sites


Get weekly Excel tips from Debra



Excel Data Entry Popup List





Excel UserForms for Data Entry


Last updated: November 13, 2018 11:49 AM
Contextures RSS Feed