613-749-4695 (Peter)
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

VBA Chapter 24 of 24: VBA Excel Command Buttons, Text Boxes , Combo Boxes and other Controls

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 VB Editor of Excel 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.

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 Section  24A of 24: Properties and VBA code for Command Buttons
VBA Section  24B of 24: Properties and VBA code for Labels
VBA Section  24C of 24: 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 Section  24D of 24: Properties and VBA code for Combo Boxes
VBA Section  24E of 24: 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 Section  24F of 24: 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 Section  24G of 24: Properties and VBA code for Spin Buttons
VBA Section  24H of 24: Properties and VBA code for Multi Pages

613-749-4695 (Peter)
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links