Declare a Control as Variable

(this method reduces MANUAL coding to a great extent... Just give it a try)

Pre-Requisite:

To understand the following concept/code, a comfortable understanding of Event driven Programming model is required, where, one gets introduced to Properties, Events and Methods: However, for a layman's understanding, Property is generally referred to as physical behaviour, for eg: background-color, fore-ground color, text-type, text-font-size, text-font-name etc..., Some properties are available at design time, some are available at run-time, and some others are available at both times, also, some properties may not be listed in the properties list (F4). Event is any user activity or system activity - User Activities examples: Mouse-Click (left button), Mouse-Right-Button-Click, Mouse-left-button-double-click, etc.. System Events like: idle event, WorkBook-Open, WorkBook-Close etc... Method is any built-in programme/procedure, for eg: SetFocus, OpenDatabase etc...

Let us first consider the terms Control and Variable

Control: is any area where user can perform a certain action (one at a time).
User Actions may be like: entering data into a given space, selecting one among the list of given entries etc.. No user can perform two actions at the same time.

Variable: simply called a a memory location: where system can store some value. This value can be changed at run-time (dynamic - through a program) or by passing a value exclusively.

Most of the event-driven-programming languages support storing controls as variable. Usage can be observed in the later part of this article.

The following picture displays text box, label/caption, Command button controls and labelled accordingly. There are other controls like list-box, combo-box, radio-button, check-box, and others...

We can consider this example to be of three parts,

Clear all text controls (ie., text boxes),
Clear all controls where Customer Information is displayed,
Clear all controls where Interest information is displayed

(Please try this example, with minor changes to the code from (2).

Case:1 - Clear all Text Controls

In VBA, Procedure looks like the following examples: for the sake of conveniency, numbered each line....

Create a procedure with name prcClearAllCtrls
Declare a variable 'ctrl' with datatype control

What Code does..?

Check if the current control is of required type with 'TypeOf' operator with FOR EACH loop.

If the current control is the required control, then replace its text to empty string, note, not NULL. Please note that deleting data from the control will not have any effect either on the data base field or variable associated with this control. Hence data in memory is distinct from data seen over screen.

How Code Works..?

Key to this procedure is FOR..EACH looping structure. This loop checks through all the controls on the user form, whenever, it find the type of control, as mentioned in the IF condition with TYPEOF operator, said action is carried out - in this case, the existing text is replaced with empty string.

To Experiment:

place some text boxes on a UserForm,
place one command button,
write the above given code in the Click_Event of Command Button.
Press F5 to Execute Code,
type some text in the text boxes,
now click Command_Button.
All the text in all text boxes will be replace with empty string.

Case:2

Clear Text from Controls

Categorise controls on UserForm:

To perform any partial clean-up, take care to name these controls seperately, at the 'Name' property of the control (to get control properties, click on control and Press F4 key). In this case, name all the controls in the first part of the screen like 'cdTxt_CFNme', 'cdTxt_CLName', 'cdTxt_CAddr1' and so on... likewise, with controls on the Intrest section, name them like 'inTxt_PAmt', 'inTxt_IntAmt', 'inTxt_IntToPay' and so on... This kind of naming convention, helps to call each control set seperately.
Apart from the above given code, the second IF Condition checks if the current control has the given condition (extreme two characters in the control name is 'cd', only then, the current controls text will be replaced with empty string.
'.controls' is one of the properties of user form,
'.name' is one of the properties of text control

Case:3 - Mixed Controls

The following code can be adopted, if the controls are named with prefix, as per the type of control, eg: txt-for text controls, cmd-for command buttons, cmb-for combo box controls, lst-for list controls, hence the control names will be like: txtCustomerName, cmbStateCode, lstZipCode etc...

Try the following examples...

On SAVE button click: only those controls without text are to be highlighted in different color.
On SAVE button click: Check if all controls have data entered into,
display message box, if atleast one of the controls donot have any data.
On SAVE button click: Mark some of the controls as mandatory, check if all the mandatory fields had data.
On CLEAR ALL button click: Place a command button, name this command button control as cmd_ClearAll, and write code to clear all data in all controls on Clear_All button click.

Consulting - Quality