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-3: VBA Excel Command Buttons, Text Boxes , Combo Boxes and other Controls
(Levels: Intermediate)

Now that you have designed you userform and added  different controls It is time to manage the properties of these controls and to develop VBA code linked to them.

When you single click on a control on the userform in the VBE its properties are shown in the Properties window. The properties and the number of properties differ depending on the type of controls that you have selected. These properties can be changed in the Properties window of the VB editor or can be changed programmatically in a VBA procedure.

For all your controls set the "Name" property that you will refer to in your VBA procedures. Use prefixes and some upper case letters in these names (cbxCitx, txbCityName). Be as descriptive as possible to make your code clearer. The prefixes that I use are: command button (cmb), labels (lbl), combo boxes (cbx), text boxes (txb), list boxes (lbx), check boxes (ckb), radio button (rdb), toggle buttons (tbt), frames (fra), tab strips (tsp), multi pages (mpg), scroll bars (scb), spin buttons (spb). The controls that particularly need to be well named are the controls which eventual values you will be using in your procedures like the text boxes, the list boxes, the combo boxes, the option buttons and  the check boxes. For example:
Range("A1").Value = txbCityName.Value
will   take the value entered by the user in the text box named tbxCityName and enter in cell A1 of the active sheet.

The "Caption" property contains the text shown on a label, a command button, a check box, an option button, a frame, a tab strip or a multi page.

Most controls have a "TabIndex" property. Instead of setting this property for each control see the "Tab Order" of the userform in lesson 3-1.

Then for most controls there are these 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.

In the following sub-sections yo will learn about the most important or usual controls. You will learn about their properties and the VBA code that can be developed within events related to them.

There are 3  controls that are widely used and they are the command buttons, the labels and the text boxes. The command button is where most of the VBA code for the userform is developed.

VBA Lesson 3-4: Properties and VBA code for Command Buttons
VBA lesson 3-5: Properties and VBA code for Labels
VBA lesson 3-6: Properties and VBA code for Text Boxes

Then there are two little more complex and powerful controls the combo boxes and the list boxes.  The difference between a combo boxes and the list boxes is that the combo box is a drop-down list and the user can submit a single value either one of the values from the drop-down list or any other value. The list box shows a certain number of values with or without a scroll bar and the user can select one or more values but not a value that is not in the list.

VBA lesson 3-7: Properties and VBA code for Combo Boxes
VBA lesson 3-8: Properties and VBA code for List Boxes

A third  group of controls appear often on userforms and they are the check boxes, the option buttons and the frames.

VBA lesson 3-9: Properties and VBA code for Check Boxes, Option Buttons and Frames

Finally 5  other controls allow the development of fancier and more complex userforms:

VBA lesson 3-10: Properties and VBA code for Spin Buttons

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