TextBox (Input Field) with Excel VBA

The TextBox control (input field) is primarily used for user text input, which can later be processed in the program, or to display calculation results from the program.

The text entered into the field can be converted in code into numbers or formulas.

The main event associated with the TextBox is the Change event.

Table. Main Properties of TextBox

Example: Adding Two Numbers

As a demonstration, let’s create a project where the sum of two numbers entered into TextBoxes is displayed in a third TextBox.

Create a form with three labels, three TextBoxes, and two buttons.

Set their properties in the Properties window as shown in Table.

Table. Property Values

In the form module, enter the code.

• Clicking OK calculates the sum of a and b and displays it in c.
• Clicking Cancel closes the form.

Button with a « Hot Key »

The Accelerator property assigns a key (letter or digit) that, when pressed with , triggers the button’s Click event.

The key must appear in the button’s Caption and will be underlined.

Example:

Private Sub UserForm_Initialize()
    cmdOK.Accelerator = "O"
    cmdCancel.Accelerator = "C"
End Sub

Now pressing +O = clicking OK, and +C = clicking Cancel.

Keys and

• Setting Default = True on a button assigns it to .
• Setting Cancel = True assigns it to .

Example:

Private Sub UserForm_Initialize()
    cmdOK.Default = True
    cmdCancel.Cancel = True
End Sub

Here, triggers OK, and closes the form.

Blocking the Result Field

• Enabled = False → disables the control completely (no focus).
• Locked = True → prevents editing, but allows focus (so content can be copied).

Example:

Private Sub UserForm_Initialize()
    txtC.Enabled = False
End Sub

Preventing Button from Taking Focus

By default, clicking a button shifts focus to it.
To keep focus on the previous control, set TakeFocusOnClick = False.

Example:

Private Sub UserForm_Initialize()
    cmdOK.TakeFocusOnClick = False
End Sub

Moving Focus Bet ween Fields with

To move focus when is pressed, handle the KeyDown event.

Example:

Private Sub txtA_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
                         ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        txtB.SetFocus
    End If
End Sub

Private Sub txtB_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
                         ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        cmdOK_Click
        txtA.SetFocus
    End If
End Sub

Private Sub UserForm_Initialize()
    txtC.Locked = True
End Sub

Tooltips

The ControlTipText property defines tooltips.
When the mouse hovers over a control, the text appears in a small window.

Example: tooltips are added to the three TextBoxes and two buttons.

Password Input Field

TextBox can be used as a password field.

• The displayed echo characters are set by PasswordChar.

Example: a form with a password field and a button.

• The button is locked by default.
• If the correct password (laru) is entered, the button unlocks; otherwise, it locks again.
• Case-insensitivity is achieved using LCase().

Table. Property Values

Multiline TextBox

To enable multiline input, set Multiline = True.

Example: Currency Conversion Project

• One TextBox is used for input (amount in rubles).
• One multiline TextBox is used for output (converted to USD).
• Input value is entered, triggers calculation, result displayed in multiline TextBox.
• The multiline box is locked to prevent editing.

Table. Property Values

Exchanging Values Between Forms

If a project contains multiple forms, they can share values using public variables declared in a standard module.

Example:

• Form1: two TextBoxes + button
• Form2: one TextBox
• Button in Form1 → reads values, adds them, closes Form1, opens Form2, and shows the result.

Module of Form1

Private Sub CommandButton1_Click()
    res = CDbl(TextBox1.Text) + CDbl(TextBox2.Text)
    Unload Me
    UserForm2.Show
End Sub

Module of Form2

Private Sub UserForm_Initialize()
    TextBox1.Text = res
End Sub

Standard Module

Public res As Double