How to Assign an Excel VBA UserForm Textbox Value to a Variable?

Sharing is caring!

In Excel VBA, you can use UserForms to create custom dialog boxes that allow users to input data. A common requirement is capturing the value entered into a textbox on the UserForm and assigning it to a variable for further processing. This article will explain exactly how to retrieve the textbox value from an Excel VBA UserForm and store it in a variable so you can use the inputted data in your macro.

Creating a Basic UserForm with a Textbox

Before we get into assigning the textbox value to a variable, let’s quickly cover how to insert a UserForm and add a textbox control:

1. In the Excel VBA editor (press Alt+F11 to access it), insert a new UserForm from the Insert menu.
2. From the Toolbox, click the Textbox control and draw a textbox on your UserForm.
3. Add a label above the textbox if desired to tell the user what to enter.
4. Resize and position the textbox and label as needed.

Your UserForm will look something like this:

Setting Textbox Properties

You can customize the appearance and behavior of the textbox by setting its properties in the Properties window (press F4 to view it):

• Name: Give your textbox a meaningful name that describes its purpose, like TextBox_NameInput. This makes it easier to reference in code.
• Font: Choose the font, size, and style for the text that will appear in the textbox.
• TextAlign: Specify whether the text should be left-aligned, centered, or right-aligned within the textbox.
• MaxLength: Set a limit on the number of characters that can be entered into the textbox. Useful for preventing users from entering too much text.
• MultiLine: Allow the textbox to accept multiple lines of text instead of just a single line.
• ScrollBars: Display a vertical and/or horizontal scrollbar when the content exceeds the textbox size. Only applicable when MultiLine is True.
• PasswordChar: Mask the characters typed into the textbox with a specified symbol, like an asterisk (*). Useful for password input fields.

Experiment with different property settings to fine-tune your textbox.

Displaying the UserForm

To show the UserForm to the user when your macro runs:

1. Create a new standard module in the VBA Project window
2. Add this code to display the UserForm:

Sub ShowUserForm()
    UserForm1.Show
End Sub

Run the ShowUserForm macro and the dialog box will appear with your textbox.

Assigning the Textbox Value to a Variable

Here’s how to capture the value the user types into the textbox and assign it to a variable:

1. Double-click on the UserForm to access its code module
2. Add this code to assign the textbox value to a variable when a command button is clicked:

Dim strInput As String

Private Sub CommandButton1_Click()
    'Assign textbox value to variable
    strInput = TextBox1.Value  

    Unload Me
End Sub

Let’s break this down:

• Dim strInput As String declares a module-level variable called strInput to store the textbox value
• TextBox1.Value returns the current value of the textbox, which is assigned to strInput
• Unload Me closes the UserForm

Referencing the Variable

After the user enters a value and clicks the command button, the UserForm closes and strInput contains the value entered. You can then reference that variable in your main macro code:

Sub ShowUserForm()
    UserForm1.Show

    'Do something with the variable value
    MsgBox "You entered: " & strInput
End Sub

Here the MsgBox displays the value of strInput captured from the UserForm textbox.

Validating User Input

It’s a good idea to validate the data entered into your UserForm textbox. You can do this in the textbox Change event:

Private Sub TextBox1_Change()
    'Validate that entered text is not empty
    If TextBox1.Value = "" Then
        MsgBox "Please enter a value"
    End If
End Sub

This code displays a message if the user leaves the textbox empty. You can add any validation logic you need here, such as checking for a certain data type or a required format.

You can also validate the input when the user clicks the command button to submit the form:

Private Sub CommandButton1_Click()
    If TextBox1.Value = "" Then
        MsgBox "Please enter a value before submitting"
        Exit Sub
    End If

    strInput = TextBox1.Value
    Unload Me
End Sub

This prevents the form from being submitted if the textbox is left blank.

Other validation ideas:

• Check for a valid number or date format
• Ensure text is within a certain character limit
• Match input against a list of allowed values
• Verify that required fields have data

By validating the textbox data up front, you can avoid errors later in your macro.

Using the InputBox Function

An alternative to creating a UserForm to get user input is to use the built-in InputBox function. It allows you to quickly prompt the user to enter a value without having to build a custom form.

For example:

Dim strInput As String

strInput = InputBox("Enter a value")

'Do something with the variable value 
MsgBox "You entered: " & strInput

This displays an input box with a message prompting the user to enter a value. What they type gets assigned to the strInput variable.

The InputBox has some limitations compared to a full UserForm:

• Only one input field can be provided
• Customization options are limited
• No events to validate input before submission

But for simple user input, the InputBox can be a quick and easy solution.

Passing the Variable to Other Macros

Once you have the textbox value stored in a variable, you can pass it to other macros as needed.

One way is to define the variable as Public at the top of a standard module:

Public strInput As String

Then you can access strInput from any macro in any module. For example:

Sub UseInput()
    MsgBox "The value entered was: " & strInput
End Sub

Another option is to pass the variable as a parameter to another macro:

Sub ShowUserForm()
    UserForm1.Show

    Call UseInput(strInput)
End Sub

Sub UseInput(strValue As String)
    MsgBox "The value entered was: " & strValue
End Sub

Here the UseInput macro accepts a string parameter, which is passed the strInput value.

Handling Multiple Textboxes

If your UserForm has multiple textboxes, you’ll need to capture the value from each one. You can assign the values to separate variables or to elements in an array.

For example, if you have two textboxes named TextBox_FirstName and TextBox_LastName:

Dim strFirstName As String
Dim strLastName As String

Private Sub CommandButton1_Click()
    strFirstName = TextBox_FirstName.Value
    strLastName = TextBox_LastName.Value

    Unload Me
End Sub

Or using an array:

Dim strTextBoxValues(1 To 2) As String

Private Sub CommandButton1_Click()
    strTextBoxValues(1) = TextBox_FirstName.Value
    strTextBoxValues(2) = TextBox_LastName.Value

    Unload Me
End Sub

Then reference the variables or array elements as needed in your main code.

Best Practices

A few tips for working with UserForm textbox values:

• Use meaningful names for your UserForm, textbox, and variables. For example, UserForm_GetName, TextBox_NameInput, strNameEntered, etc. This makes your code more readable.
• Validate user input to ensure you’re getting the type of data you expect. Check for empty values, incorrect data types, invalid formats, etc.
• Clear the textbox after capturing the value so it’s ready for the next input. You can use TextBox1.Value = "" to empty the contents.
• Handle errors in case the user enters something unexpected or closes the UserForm without entering anything. Use error handling to prevent crashes.
• Test thoroughly with different inputs to make sure your code behaves as expected in all scenarios before deploying to users.

Final Thoughts

Assigning an Excel VBA UserForm textbox value to a variable is an essential skill when you need to capture user input for your macros. The basic process is straightforward:

1. Create a UserForm with a textbox control
2. Display the UserForm using the Show method
3. Assign the TextBox.Value to a variable in the code-behind
4. Reference the variable elsewhere in your VBA code

By adding validation, error handling, and following best practices, you can create robust user input forms. Alternatives like the InputBox function are also handy for simple input.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.