Home > Macros > Basics > Text Box Macro for Worksheet Text BoxHow 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.
|
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
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:
To manually create the Text Box on the worksheet, follow these steps:
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
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
These two macros will format all the ActiveX Text Boxes on the active Excel worksheet.
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
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.
Last updated: May 14, 2023 1:21 PM