Program by Levels

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple

VBA Lesson 3-6: The Text Boxes in VBA for Excel
(Levels: Intermediate, Advanved)

In the toolbox the text box  has this icon   .

The text box is the simplest control to require an entry from the user. The user types something in it and this value can then be used in your VBA procedure. You will usually add a label to accompany the text box.

For most controls including the VBA for Excel text box there are general properties that allow you to set the font, the color of the font, the color of the background, the type of background, the type of border and other design features. Using the 3 windows in the Visual Basic Editor you will see the following properties in the "Property" window when the text  box is selected.

Properties

The other interesting properties of the text boxes  are:

- WordWrap to be able to write more that one line on a button,
- ControlTipText which generates a small comment box when the user moves the mouse over the control. You can use this property to give explanations and instructions about the command button,
- Enabled and Visible are properties that you can change programmatically to disable or render invisible a command button following a previous selection in another control of the userform,
- TabIndex is a property that you change through the "Tab Order" functionality as shown in the UserForms  section.
- MaxLength to limit the number of characters entered by the user,
- Value or Text which is the text show in the text box when the userform is activated ("Enter your Name" for example)

To ask users to submit a password to run certain macros develop a userform with a text box and a command button. In the text box you can modify the "PasswordChar" property so that when the user enters the password nobody around can read it. Use an asterisk, an ampersand or any other character in it.

Code

The most important ting to remember is that a text box is what its name says it carries text. So if you want to send a numerical value from a text box to a cell you must use the "Val" thing:
Range("A1").Value=Val(tbxInput)

The same applies when you want to use text box vale in mathematical operations:
tbxInputTot= Val(tbxInput1) + Val(tbxInput2)

One of the limitations of the text box is the format. If you want to show dates, numbers with decimals or with a coma for the thousands you need to program some procedure within the "Change" event of the text box.

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple