Contextures

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

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search

Search Contextures Sites

Search Contextures Sites

 

Excel Data Entry Popup List

 

 

 

 

Excel UserForms for Data Entry

 

Last updated: September 23, 2016 7:26 PM
Contextures RSS Feed