An Excel VBA TextBox is a text-entry control that lets a workbook user type information, such as a name or search term, into a worksheet interface or a VBA form. On Windows, add an ActiveX TextBox from Developer > Insert > ActiveX Controls; use a UserForm TextBox for a pop-up form, or a regular worksheet text box when you only need displayed text.
What “TextBox” Means in Excel
Excel has several things that look like text boxes, but they do not behave alike. The right choice depends on whether someone needs to type data for a macro to use, whether the text belongs in a worksheet cell, and whether the interface should appear on the sheet or in a separate dialog.
- ActiveX TextBox: A worksheet control with properties and events that VBA can respond to. It is useful for a simple on-sheet input field on Excel for Windows.
- UserForm TextBox: A Microsoft Forms control placed on a custom VBA dialog. It suits workflows that need several fields, labels, and buttons in a compact window.
- Shape text box: A drawing object added from the Insert tab. It displays text on a worksheet, but is not an ActiveX input control and does not provide the same control events.
Excel also has Form Controls, but a TextBox is not one of the standard Form Controls in the Developer tab’s Insert menu. The similarly named ActiveX control is the usual worksheet control when you want a VBA TextBox.
Choosing Between a Worksheet Control and a UserForm
Use a worksheet ActiveX TextBox when users should enter or see a value directly beside the data it relates to. A linked cell can pass the value into the grid without a custom event procedure. This is convenient for a small input area, but the control remains part of the sheet layout and can be moved or resized while Excel is in Design Mode.
Choose a UserForm when the task is more like a guided dialog: for example, collecting a customer name, date, and category before a macro writes a new row. A form can keep input fields together and validate them before saving. It takes more VBA setup than a linked worksheet control, and the form’s code and controls are managed in the Visual Basic Editor rather than directly on the grid.
If the workbook only needs a prompt and one response, VBA’s built-in InputBox function may be simpler than either control. For a permanent label, instruction, or title, a shape text box is usually more appropriate. Decide based on the job, not just on which object happens to be called a text box.
Adding an ActiveX TextBox to a Worksheet
These steps apply to Excel for Microsoft 365 on Windows. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon, then select Developer. To insert the control:
- Open the Developer tab and select Insert.
- Under ActiveX Controls, select Text Box.
- Drag on the worksheet to draw the field at the size you want.
- Keep Design Mode on while setting it up. Select the control and choose Properties on the Developer tab to inspect its settings.
- Change the control’s (Name) property to a descriptive identifier, such as
txtCustomer, if that will make your VBA clearer. The control’s name is distinct from any text it displays.
Click Design Mode to turn it off when you want to use the field normally. With Design Mode off, a user can type into the TextBox. With Design Mode on, clicking the object selects it for editing instead. To change the field’s size or properties later, turn Design Mode back on.
Linking the TextBox to a Worksheet Cell
For a straightforward input field, set the ActiveX TextBox’s LinkedCell property in the Properties window to a cell reference such as B2. The control and that cell are linked, so the typed value is available in the worksheet and can be read by formulas or other macros. A linked cell is often easier to maintain than code that copies every keystroke.
Use a dedicated input cell and label it clearly. For instance, place “Customer name” in A2 and link the TextBox to B2. If a macro later processes the value, it can read B2 rather than depending on the TextBox being active. Test the link with Design Mode off, and verify that the cell contains the expected value before building more automation around it.
Other useful properties include MultiLine for allowing more than one line, EnterKeyBehavior for controlling how Enter works in a multiline field, and MaxLength for limiting the number of characters. Property names and available settings can depend on the control. Do not assume that a cell’s formatting, validation rules, or behavior automatically transfers to the TextBox.
Reading Input with a VBA Event
ActiveX controls can run event procedures. For example, a TextBox’s Change event runs as its value changes, so it can copy each edit to a cell. To create the event procedure, turn on Design Mode, double-click the control, and use the code window for the worksheet containing it. The example assumes the worksheet control is named TextBox1 and writes its value to B2:
Private Sub TextBox1_Change()
Dim inputText As String
' Copy the current entry to the worksheet cell.
inputText = CStr(Me.TextBox1.Value)
Me.Range("B2").Value = inputText
End Sub
Excel creates the event procedure shell when you double-click the control; the code belongs in that worksheet’s code module, not in a standard module. If you renamed the control, use the name that appears in its (Name) property and in the event procedure. The Change event may run once for each character typed, so it is appropriate for immediate updates but not always for tasks that should happen only after the user finishes typing. For validation or a final action, a button or another event may be a better fit.
Before running a macro, save a copy of the workbook. Macros cannot be undone with Ctrl+Z, and an event procedure can write to cells as the user types. Store a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm); saving it as a macro-free .xlsx file removes the VBA project.
Creating a TextBox on a UserForm
To build a form, open the Visual Basic Editor with Alt+F11 on Windows. In the editor, choose Insert > UserForm. If the Toolbox is not visible, show it from the View menu, then select TextBox and draw it on the form. Add labels and command buttons as needed. Set the TextBox’s (Name) property to a useful name such as txtEmail, and give buttons descriptive names such as cmdSubmit.
Code for a UserForm control belongs in the UserForm’s code module. For example, a Submit button can check that the text field is not blank and then place the value in a worksheet cell. A complete workflow should also decide what to do after entry, such as close the form, clear the field, or display a validation message. For multi-field forms, validate each required value before writing anything to the sheet, so incomplete entries do not create partly filled records.
Use Me inside a UserForm procedure to refer to that form. A UserForm TextBox and a worksheet ActiveX TextBox are related Microsoft Forms controls, but they live in different containers and their code belongs in different modules. That distinction is a common source of “object not found” errors when code is copied from one kind of control to the other.
TextBox Versus a Shape Text Box
A shape text box is added from Insert > Text > Text Box. It is useful for worksheet instructions, headings, or a designed dashboard. It does not behave as a VBA TextBox control: it has no ActiveX TextBox Change event, and typing into its displayed text is not the same as entering a value into a linked cell.
VBA can read text held in a worksheet shape, but that requires referencing the shape and its text frame rather than using a TextBox control’s Value property. That approach is suitable when a macro needs to inspect text stored in a drawing object, but it is generally a less direct input interface. For users entering data, a linked worksheet control, a UserForm, or a normal cell with data validation is usually easier to understand and maintain.
Compatibility, Security, and Common Problems
ActiveX controls are a Windows-specific choice: Excel for Mac does not support worksheet ActiveX controls. UserForms can be used in Excel for Mac, but availability and behavior can differ from Windows, so test a form on the target platform before distributing a workbook. Excel for the web cannot create, edit, or run VBA macros. A workbook may be opened there, but VBA-based interactivity should be used in desktop Excel.
Macros are also subject to Office security settings. Excel may block macros in files from untrusted locations or show a security warning; users should enable macros only for workbooks they trust and expect to use. Do not advise users to lower security settings globally just to make one workbook run.
Common issues and practical checks include:
- The control will not accept typing: Turn off Developer > Design Mode. Design Mode is for selecting and changing the control, not using it.
- The event does not run: Confirm the procedure is in the worksheet or UserForm module that owns the control, and check that the event name matches the control name.
- The value is missing: Check the LinkedCell reference, or confirm that the code reads the correct control and writes to the intended sheet and cell.
- The workbook works on one computer but not another: Check the Excel platform and macro security. ActiveX-based worksheet controls are not portable to Mac Excel or Excel for the web as working controls.
- Ctrl+Z does not reverse a macro: That is expected. Save a backup before running VBA that changes workbook data.
Frequently Asked Questions
How do I add a TextBox in Excel VBA? On Windows, select Developer > Insert > Text Box under ActiveX Controls, draw it on the sheet, and use Design Mode and Properties to configure it. For a pop-up interface, insert a UserForm in the Visual Basic Editor and add a TextBox from the Toolbox.
How do I get a TextBox value into a cell? Set the worksheet ActiveX TextBox’s LinkedCell property to a cell such as B2. Alternatively, use a VBA event procedure to assign the control’s Value to a cell. A UserForm TextBox needs code to transfer its value when the user submits the form.
Why can’t I type in my Excel TextBox? If it is an ActiveX control, check whether Developer > Design Mode is on. Turn it off to use the control. Also check that the sheet is not protected in a way that prevents interaction and that you are using desktop Excel on Windows for an ActiveX control.
Can Excel for Mac run an ActiveX TextBox? No. Excel for Mac does not support worksheet ActiveX controls. A UserForm may be an alternative, but test its behavior on the Mac version you intend to support; Excel for the web does not run VBA.
What is the difference between a TextBox and a cell? A cell is part of the worksheet grid and can hold data directly. A TextBox is an interface object placed on a sheet or form. A linked ActiveX TextBox can connect to a cell, but a shape text box is primarily for displaying text and is not a substitute for a cell input field.
Related
- Can You Insert a Blank Row in an Excel PivotTable?
- Conditional Formatting Based on a Checkbox in Excel
- Draw.io Merge Cells: Steps, Limits, and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Filter Range: AutoFilter Examples and FixesTo filter a range with Excel VBA, use the range’s AutoFilter method and specify the field number and…
- Excel VBA Set Column Width: Code, Units, and Common FixesTo set a column width in Excel VBA, assign a value to the column’s ColumnWidth property, such as…
- VBA Array Clear: How to Erase, Reset, and Reuse ArraysTo clear a VBA array, use Erase arrayName. For a dynamic array, Erase releases its allocated storage; for…
- Dim ws As Worksheet in VBA: Meaning and UseDim ws As Worksheet declares a variable named ws that can refer to an Excel worksheet, but it…
- Excel VBA Copy and Paste Cells: Examples and OptionsTo copy a cell with Excel VBA, use the source range’s Copy method and specify a destination: sourceCell.Copy…
- VBA Create Word Document From TemplateTo create a Word document from a template with VBA, call Word’s Documents.Add method with the template’s full…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.