TextBox

The TextBox control is commonly used in a Userform to get data from users. It can also be used to just show the data instead (may be disabled to receive input). As with many other controls, we can format the size, set the position, apply color, borders, format the font, and much more, targeting each of the corresponding properties. Some properties and also events of the TextBox can help to take full control of user input, for example limiting the length or number of characters entered, preventing from adding certain characters, or allowing only numbers or only letters, etc. The toolbox symbol for a TextBox control is highlighted in the image below.

 

TextBox Properties

As with other controls (and the Userform), the Name property is key and works as an object to target any other property or method. We can change the default name of the text box in the properties window.

 

 

Then we can use that name (as object) to apply any other property (or a method). For example, the code below sets the font size and color of the text in the box. That could go inside the Userform module (most likely the Userform_Initialize procedure). It could also run in a standard module by referencing the Userform first (UserForm1.TextBox1….).

 With TextBox1  'or Me.TextBox1
     .Font.Size = 12
     .ForeColor = vbRed
 End With

The default property for a TextBox is the Value property. That’s how we get or set the value in the box. As explained earlier in this training (see Userform Controls), the Text property can also be used to get or set the contents in a text box. However, the Text property returns the current contents of the text box while the Value property gets the saved value of the box. The Text property requires the text box to have focus. Furthermore, it gets text (string) content, so when empty returns vbNullString, while Value returns Empty. When working with numbers is better to use Value, or have the VBA Function Val to ensure it is numeric. The code example below gets the value in a text box when submitting the form by clicking a button (CommandButton1), and calls another macro that will do something with that input.

 Private Sub CommandButton1_Click()
     userInput = TextBox1.Value
     Call otherMacro(userInput)
 End Sub

There are several properties to manage the size of the text box and the extent of its contents. The properties Width and Height determine the size of the box (they are usually set at design-time). On the other hand, we can use the property AutoSize to let the box dynamically grow while characters are being added (used seldom). We can align the text to the left, right, or center, with TextAlign. We can allow contents in multiple lines (MultiLine property) and/or wrap the text (WordWrap). This is useful when having bigger text boxes intended to accommodate comments or other type of large sentences or paragraphs. We may want to limit the length instead, for that we use MaxLength.

 

TextBox Events

When selecting the TextBox object in the left drop-down in the Userform code window, we can see the list of events for a text box on the left.

 

Despite most Userform controls share the same list of events (with some exceptions), certain events are used with some more than other. For example, the Click and DblClick events are not used that much with the TextBox (while Click is the key event for a CommandButton control). Events often used with the TextBox control are those triggered when the contents in the box change (Change event, but also BeforeUpdate and AfterUpdate events). The event procedure below runs when changing the value in the text box, and then calls a function to see if the value (a name for example) is found in the worksheet or DB, and returns a message if not found.

 Private Sub TextBox1_Change(ByVal Cancel As MSForms.ReturnBoolean)
     Dim user As String
     user = TextBox1.Value
     result = SearchUser(user)
     If result = False Then MsgBox "Cannot find user " & user
 End Sub

 

We can also use mouse-related events or key-stroke related events to determine whether the input meets certain criteria. The key events KeyDown, KeyPress, and KeyUp, occur in that particular order and accept a parameter that returns the key VB code or ASCII code of the key pressed. That can be used to prevent certain characters being entered or certain control keys used while adding text to a box. The example below makes sure the user only enters numbers in the text box by locking the box when not a number. Find the full list of ASCII codes in thisother page.

 Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
     If KeyAscii < 48 Or KeyAscii > 57 Then 'ascii for numbers from 0 to 9
         TextBox1.Locked = True 'lock if NAN
     Else
         TextBox1.Locked = False
     End If
 End Sub

The events KeyDown and KeyUp accept a KeyCode parameter instead. There is a key code for each key in the keyboard. It is often used with control keys. For example, we can see if ESC is used while adding input to a text box by checking the KeyCode is vbKeyEscape and then for example close the form (see code below). See the full list of KeyCode constants in this other page.

 Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
     If KeyCode = vbKeyEscape Then
         Unload Me
     End If
 End Sub

 

<<< Previous | Next >>>