In Excel VBA, read a UserForm TextBox with its Value property: for example, userName = Me.txtName.Value inside the form, or userName = UserForm1.txtName.Value from another procedure. Use the TextBox’s actual control name, and read the value before unloading the form if you need it after the form closes.
Read a TextBox value in the UserForm
The direct approach is to refer to the TextBox by name and get its Value. In the UserForm’s code module, Me means the form instance whose code is running. This is often the clearest choice in an event procedure, such as a button click.
Private Sub cmdOK_Click()
Dim enteredName As String
' Read the value typed into the TextBox.
enteredName = Me.txtName.Value
MsgBox enteredName
End Sub
Here, txtName must be the TextBox control’s (Name) property, not its displayed label or the text shown beside it. To check or change the name, open the UserForm in the Visual Basic Editor, select the TextBox, and inspect the Properties window. The Caption property belongs to controls such as labels and command buttons; a TextBox’s entered content is its Value.
For a TextBox, Value is the property normally used to retrieve what the user entered. It can be read without requiring that the TextBox currently has focus. The Text property also relates to the displayed text, but it can depend on the control having focus and is not the dependable general-purpose choice for reading the input. Prefer Value unless you have a specific reason to use Text.
Write the value to a worksheet when a button is clicked
If the form should save the entry immediately, put the read and the worksheet assignment in the command button’s Click event. The form and worksheet are separate objects: first get the TextBox value, then assign it to a range.
Private Sub cmdSave_Click()
Dim enteredName As String
' Copy the form entry to cell A1 on Sheet1.
enteredName = Me.txtName.Value
ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = enteredName
Unload Me
End Sub
Rename cmdSave and txtName in the example to match your controls, and replace Sheet1 and A1 with the intended worksheet and cell. ThisWorkbook means the workbook that contains the VBA code; it is often safer than ActiveWorkbook, which can refer to a different workbook if the user switches workbooks. If the user can submit several records, write to a deliberately chosen next row rather than repeatedly overwriting A1.
A form’s event code runs when that event occurs. If the code is in the UserForm module, Me.txtName.Value makes the reference explicit. The equivalent reference within that form module is txtName.Value, but Me helps distinguish the form’s control from a variable or control with a similar name elsewhere.
Get the value after showing the form
You can also show a form from a standard module and read the entry after the form is dismissed. A UserForm shown modally pauses the calling procedure until it is hidden or unloaded. To preserve access to its TextBox after the user clicks OK, hide the form in the button event; after .Show returns, read the value and then unload the form.
Private Sub cmdOK_Click()
' Hide the form so the calling procedure can read its controls.
Me.Hide
End Sub
Sub PromptForName()
Dim enteredName As String
' Show the default instance of the UserForm modally.
UserForm1.Show
enteredName = UserForm1.txtName.Value
' Release the form after its value has been copied.
Unload UserForm1
MsgBox enteredName
End Sub
This example assumes the form is named UserForm1, its TextBox is named txtName, and its OK button runs the first procedure. The key distinction is Hide versus Unload: hiding leaves the form instance and its controls available, while unloading removes that instance. If the OK event unloads the form, the calling code cannot rely on reading the old control value after .Show returns. A newly loaded instance is not a safe substitute for the instance the user filled in.
Plan for cancellation as well. Clicking the title-bar X can close or unload a form without following the OK path. If the caller must distinguish an accepted entry from a cancellation, store an explicit result flag or status in the form and check it after .Show. Do not treat an empty TextBox alone as proof of cancellation: an empty entry may be valid, and a user may also cancel after typing something.
Return a value through a form property
For a form that is reused by different procedures, a public property can give callers a clear interface instead of making them reach directly into a control. Put this property procedure in the UserForm’s code module:
Public Property Get EnteredName() As String
' Expose the TextBox value to code using this form.
EnteredName = Me.txtName.Value
End Property
After showing and hiding the same form instance, the caller can use UserForm1.EnteredName. The property does not automatically keep a separate copy; it returns the current TextBox value when called. This separation is useful when you later rename the TextBox or add validation, because calling code can continue to ask the form for an entered name rather than depending on the internal control name.
Another option is to copy the value into a Public variable in a standard module when the user clicks OK. That can work in a small macro, but it introduces shared state: another procedure may change the variable, and it is less obvious which form supplied the value. A form property or a value copied directly to a worksheet is generally easier to follow. For larger projects, consider returning a result from a dedicated procedure or class rather than exposing many global variables.
Validate and convert the input
A TextBox supplies user-entered text; it does not guarantee that the entry is a number, date, or nonblank value. Check the input before converting it or using it in calculations. For example, trim surrounding spaces and test a numeric entry before calling CDbl.
Private Sub cmdSave_Click()
Dim rawAmount As String
Dim amount As Double
' Remove spaces at the beginning and end.
rawAmount = Trim$(Me.txtAmount.Value)
If Len(rawAmount) = 0 Or Not IsNumeric(rawAmount) Then
MsgBox "Enter a numeric amount."
Exit Sub
End If
' Convert only after checking the entry.
amount = CDbl(rawAmount)
ThisWorkbook.Worksheets("Sheet1").Range("B2").Value = amount
End Sub
IsNumeric and CDbl interpret values according to the system’s regional settings, including decimal and thousands separators. If people using different regional settings must enter a fixed format, define and validate that input format deliberately rather than assuming a comma or period always means the same thing. For dates, use an appropriate date check and conversion, and consider whether an unambiguous entry format is needed. For ordinary text, do not convert at all; retain the value as a string.
Validation should match the task. A required name might be checked for zero length after trimming; an optional comment should usually be allowed to remain blank. If invalid input is found, show a useful message and leave the form open so the user can correct it. Avoid silently replacing the user’s entry with a default unless that behavior is intentional.
Common problems and how to fix them
- Wrong control name: The code refers to a label caption or an assumed name rather than the TextBox’s (Name) property. Select the control in the Visual Basic Editor and confirm its exact name, including spelling.
- Reading after unloading: If the form was unloaded, its controls are no longer the same live instance. Copy the value before unloading, or hide the form and let the caller read it before unloading.
- Using the wrong form reference:
Meis available in a form’s own code module, not a standard module. From a standard module, refer to the displayed instance correctly, such asUserForm1.txtName.Valuewhen using that default instance. - Unexpected blank results: The user may not have typed anything, the code may be reading the wrong TextBox, or another routine may clear the control. Check the control name and inspect the value immediately before it is used.
- Type mismatch or bad conversion: A TextBox can contain letters, spaces, or formatting characters. Validate the content before assigning it to a numeric variable or converting it.
- Worksheet reference error: Confirm that the sheet name exists in the workbook and that the range reference is valid. A worksheet codename, tab name, and workbook name are distinct ways of identifying objects.
When debugging, pause the code at the line that reads the value and inspect Me.txtName.Value in the Locals or Immediate window. The Visual Basic Editor lets you step through a procedure with F8 on Windows. On Mac, the Visual Basic Editor and debugging controls are available, but keyboard mappings can differ; use the editor’s Debug menu or displayed shortcut rather than assuming every Windows key combination applies.
Choose where the value should be read
The right location depends on what should happen after the user enters data. Reading inside the button event is direct when the event itself writes to a cell, validates input, or starts another action. Reading after .Show suits a prompt-like form in which a calling macro waits for a response. A property is useful when several callers need the same form result and you want to keep control details inside the form.
- Use the button event when the form owns the action and should save or process the value immediately.
- Use a modal form and read after it closes when the calling procedure should continue with the answer. Hide the form on acceptance, and define how cancellation is reported.
- Use a property when callers should request a meaningful result without depending on the TextBox’s internal name.
- Use a worksheet cell as the destination when the entered value is intended to be stored in the workbook, not merely passed between procedures.
These patterns are alternatives, not steps that must all be combined. In particular, do not copy the same entry into a global variable, a property, and a worksheet without a reason; multiple copies can become inconsistent. Decide which part of the program owns the value, then transfer it once at a clear point.
Excel versions, platforms, and macro safety
For Excel for Microsoft 365 on Windows, UserForms are VBA forms edited in the Visual Basic Editor. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer. Open the editor with Developer > Visual Basic, or press Alt+F11 on Windows. On Mac, use the Visual Basic Editor from the Developer tab or Tools menu depending on the ribbon layout; keyboard shortcuts differ from Windows. Basic VBA UserForms are available in desktop Excel for Mac, but some controls, libraries, and Windows-specific API calls are not portable.
Excel for the web does not run VBA macros and cannot use a VBA UserForm in the browser. A workbook containing VBA must be opened in desktop Excel to run that code. Saving a macro-enabled workbook as an appropriate macro-enabled file type, such as .xlsm, preserves VBA; standard workbook formats may not retain the macro project.
Before running a macro that writes to cells or changes workbook content, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z, so a mistaken write may not be recoverable through Excel’s normal Undo command. Test the procedure on a copy or a harmless destination first, especially if the form writes to multiple rows or changes existing data. Only enable macros in workbooks you trust.
Frequently Asked Questions
How do I get a TextBox value in a UserForm button click?
In the button’s Click event, assign Me.txtName.Value to a variable or use it directly. Replace txtName with the control’s actual (Name) property.
Should I use Value or Text in VBA?
Use Value for normal retrieval. It can be read whether or not the control has focus. The Text property can be focus-dependent, so it is not the usual choice for a general read.
How do I read a UserForm TextBox from a module?
Show the form, keep the same instance available, then refer to its TextBox, for example UserForm1.txtName.Value. If the caller reads after .Show, hide the form on acceptance and unload it after copying the value.
Why is my TextBox value blank after the form closes?
The code may be reading a different or newly created instance, or the original form may have been unloaded. Copy the value before unloading, or hide the form so the calling procedure can read that same instance.
Can I put a UserForm value directly into a cell?
Yes. In a form event, assign the value to a qualified range, such as ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = Me.txtName.Value. Use the correct workbook, sheet, and destination for your file.
Related
- Excel Text to Columns: How to Make Results Vertical
- Excel Table Structured References: Rules, Examples & Tips
- Excel Take Data From a Table: Methods and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Open File Dialog: Code and OptionsIn Excel VBA for Windows, use Application.FileDialog(msoFileDialogFilePicker) to display a file picker, let the user choose a file,…
- Excel VBA: Save a Workbook as a New File with SaveAsTo save an Excel workbook as a new file with VBA, use the workbook’s SaveAs method and supply…
- Excel VBA AutoFilter Sort: Filter and Sort Data RowsTo filter and sort rows with Excel VBA, apply AutoFilter to the data range, then configure that range’s…
- Excel VBA Hyperlink: Add, Edit, and TroubleshootExcel VBA does not have a built-in Hyperlink() function: to create a clickable link in a macro, use…
- Excel VBA Format Cell Color: Fill, Font, and RulesTo format a cell’s fill color with Excel VBA, set its Interior.Color property; for example, Range("A1").Interior.Color = RGB(255,…
- Google Sheets Macro Recorder: How to Record and RunYes. Google Sheets has a built-in macro recorder: in a spreadsheet open in a desktop browser, choose Extensions…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.