Text Box (TextBox) in a Custom UserForm, Excel VBA

The TextBox control is mainly used for user input, which is then used in the program, or to display the program’s calculation results.

The text entered in a TextBox can be converted into numbers or formulas in the code. The main event associated with the TextBox is the Change event.

Table 1: Basic TextBox Properties

Adding Two Numbers

As an example using text boxes, let’s create a project where the sum of two numbers entered in two TextBoxes is calculated and the result is shown in a third TextBox, as shown in the figure below.

Create a UserForm with three Labels, three TextBoxes, and two CommandButtons. Use the Properties Window to set their values as shown.

Table 2: Property values defined in the Properties Window

In the form module, type the following code:

Private Sub cmdOK_Click()
    Dim a As Double, b As Double, c As Double
    a = txtA.Text
    b = txtB.Text
    c = a + b
    txtC.Text = c
End Sub

Private Sub cmdANNULER_Click()
    Unload Me
End Sub

Comments:

• The two numbers are entered into TextBoxes txtA and txtB, and the sum is calculated in txtC when the OK button is clicked.
• The syntax Unload Me closes the form when the CANCEL button is pressed.

Keyboard Shortcut Button

The Accelerator property of a control specifies a letter or number key that, when pressed along with the <Alt> key, triggers the control’s click event. This key must be part of the Caption string and appears underlined.
For example, to associate <Alt> + O with OK and <Alt> + A with CANCEL:

Private Sub UserForm_Initialize()
    cmdOK.Accelerator = "O"
    cmdANNULER.Accelerator = "A"
End Sub

<Enter> and <Esc> Keys

• The Default property set to True designates the button triggered by pressing <Enter>.
• The Cancel property set to True designates the button triggered by pressing <Esc>.

This means <Enter> finds the sum and <Esc> closes the form.

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

Locking the Result TextBox

• The Enabled property set to False disables the control completely (no focus).
• The Locked property set to True prevents editing while still displaying content.

Example:

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

Prevent Button from Taking Focus

By default, clicking a button gives it focus. To keep focus on the current control, set TakeFocusOnClick to False:

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

Move Focus with <Enter>

Use the KeyDown event to detect when the <Enter> key is pressed and move focus accordingly using SetFocus.

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

Comments:

• The KeyDown event occurs when a key is pressed.
• The KeyUp event occurs when the key is released.

Syntaxes:

Private Sub Object_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As fmShiftState)
Private Sub Object_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As fmShiftState)

Parameters:

Table 4: Shift Key Parameters

Common KeyCode Constants:

To move focus, use Object.SetFocus.

ToolTip Text

Controls can have ToolTips using the ControlTipText property, which displays help text when the mouse hovers over the control.
Example:

Private Sub cmdOK_Click()
    Dim a As Double, b As Double, c As Double
    a = txtA.Text
    b = txtB.Text
    c = a + b
    txtC.Text = c
End Sub

Private Sub cmdANNULER_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    txtA.ControlTipText = "Value of a"
    txtB.ControlTipText = "Value of b"
    txtC.ControlTipText = "Value of c"
    cmdOK.ControlTipText = "Sum of a + b"
    cmdANNULER.ControlTipText = "Cancel operation"
End Sub