Microsoft Office Tutorials and References
In Depth Information
TextBoxes
Sometimes you will want a TextBox to accept only numeric entries, such as a dollar figure, or a
calendar year, or a person’s age in years. The following code monitors each keystroke entry into
TextBox1, and disallows any character that is not a number. As a courtesy to the user, a message
appears to immediately inform the user that an improper character was attempted and disallowed.
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57
Case Else
KeyAscii = 0
MsgBox “You typed a non-numeric character”, _
vbExclamation, _
“Numbers only, please!”
End Select
End Sub
In the preceding code example, you might not be familiar with the term “ASCII”
(pronounced “askee”), which is an acronym for American Standard Code for
Information Interchange. Computers can only understand numbers, so a
numerical representation is needed for alphanumeric characters and other symbols
such as # and @. In the preceding code, numbers 0–9 are recognized by virtue of
their ASCII representation of 48–57. If you’d like to see a list of all 255 ASCII
and Extended ASCII characters, you can produce it yourself on an Excel
worksheet by entering the formula =CHAR(ROW()) in cell A1, and copying it down to
cell A255. Each cell will hold a character (some characters will not be visible)
whose ASCII number will correspond to the cell’s row number.
TextBoxes can display calculated results, and when using numbers for mathematical operations,
you’ll need to use the Val function, which returns the numbers contained in a TextBox string as a
numeric value. Suppose your UserForm contains seven TextBoxes, into which you enter the sales
dollars for each day of the week. As shown in Figure 19-4, an eighth TextBox can display the sum
of those seven numbers when a CommandButton is clicked, with the following code:
Private Sub CommandButton1_Click()
Dim intTextBox As Integer, dblSum As Double
dblSum = 0
For intTextBox = 1 To 7
dblSum = dblSum + Val(Controls(“TextBox” & intTextBox).Value)
Next intTextBox
TextBox8.Value = Format(dblSum, “#,###”)
End Sub
 
Search JabSto ::




Custom Search