Suggestions Example File
VBA check of userinput in a Userform

1 Introduction

To guide/support/check userinput you can apply Userforms.
You will need a bunch of code if you want to prevent every possible 'error'.
A classmmodule can reduce the amount of necessary code.
The same applies to the checking of userinput by ActiveX-controls in a worksheet.
An explanation for ActiveX controls you'll find in this page.

We'll illustrate this approach with Textboxes in a userform.
Let's assume the userform contains 10 Textboxes: their names being text1, text2 .... text10.
The content of a textbox can be adapted by the user.
To check this input you can use the 'change' event of the textbox.
Every adaptation in the textbox will trigger this event.

2.1 Userform method I

To check whether each textbox contains any text you can use this code.
If all textboxes contain some text the button 'btn_Next' will become visible.
Private Sub Text1_Change()
For j=1 To 10
If Trim(Me("Text" & j).Text)="" Then Exit For
Next
Me("btn_Next").Visible = j =11
End Sub

Private Sub Tex2_Change()
For j=1 To 10
If Trim(Me("Text" & j).Text)="" Then Exit For
Next
Me("btn_Next").Visible = j =11
End Sub

Private Sub Text3_Change()
For j=1 to 10
If Trim(Me("Text" & j).Text)="" Then Exit For
Next
Me("btn_Next").Visible= j =11
End Sub

etc.

2.2 Userform method II

A more efficient method is:
Private Sub Text1_Change()
m_next
End Sub

Private Sub Text2_Change()
m_next
End Sub

Private Sub Text3_Change()
m_next
End Sub

Private sub m_next()
For j=1 to 10
If Trim(Me("Text" & j).Text)="" Then Exit For
Next
Me("btn_Next").Visible = j =11
End Sub
But still the Change event of every textbox must contain code to trigger the check.

2.3 Userform method III

This could be a way to reduce the code:
- put all textboxes into a collection or an array
- link the checking-macro (like m_next) to the change event of every item in the collection/array

All those links will be part of the collection/array.
Every change in a textbox will trigger a corresponding link in the collection/array.
But, you can only link an event using a 'WithEvents' variable.
The use of 'Withevents' variables is restricted to a classmodule.
Therefore you need a classmodule to put this method into practice.

2.3.1 Classmodule

To begin with we name the classmodule (ad libitum) 'inputcheck'.
Every control (Textbox, combobox, checkbox, optionbutton, commandbutton, etc) has it's own specific events.
So you have to define a separate 'event' variable for every controltype.
And you have to specify an 'event' for that variable.
This means that the 'event' variable 'replaces' the corresponding userform control; the event in the classmodule 'replaces' all eventprocedures of the corresponding Userform control.

NB. You can do a lot more with a classmodule, but here we restrict ourselves to intercepting events of userform controls.

2.3.2 Which code is in the classmodule ?

1. A public object-variable that represents all possible events of a certain controltype, i.c. textboxes.
Public WithEvents cl_textbox As MSForms.TextBox
Although any valid variable name is allowed, I prefer a suffix that indicates a control is concerned (suffix 'cl'); after that an indication of the kind of control (i.c. 'textbox')

2. An event procedure for the objectvariable 'cl_textbox'.

In this case this can only be an event that applies to a TextBox.
For example the events 'Change' and 'DblClick'
Private Sub cl_textbox_Change()
-------
End Sub

Private Sub cl_textbox_DblClick()
-----
End Sub
This 'Change' event procedure in the classmodule substitutes all textbox 'Change' procedures in the userform.

2.3.3 Which code is in the Userform ?

All userform controls that have to be controlled by the classmodule have to be connected to the classmodule.
These connection have to be made in the Userform_Initialize event.
You can either use a collection to store those connections or an array.
We'll discuss them separately.

2.3.4 the use of a Collection

Declare a 'public' variable as a new collection
Public ctl_collection as New Collection
Any valid variable name is allowed. I prefer a suffix to indicate it's a collection of controls 'ctl_'.
The use of an underscore prevents any coinciding with a reserved name in VBA.

In the event procedure Userfom_Initialize:
Private Sub Userform_Initialize()
For Each ct in Controls
ctl_colletion.Add New inputcheck, ct.Name
If TypeName(ct)="TextBox" Then Set ctl_collection(ct.Name).cl_textbox = ct
Next
End Sub
Link every control in the userform to the classmodule 'inputcheck' by adding a new instance of the classmodule to the collection.
Put that link into the collection 'ctl-collection'.
Use the control's name as key in the collection, so the control's name can be used to address that item in the collection.
ctl_collection.Add New inputcheck, ct.Name
Connect the control via the link in the collection to the appropriate 'WithEvents'-variable in the classmodule.
The event variable for textboxes in the classmodule is 'cl_textbox'.
Since the textbox in the userform is an object we need the instruction 'Set' to connect it as an object to the objectvariable 'cl_textbox'.
set ctl_collection(ct.Name).cl_textbox = ct
If the user changes any textbox in the userform the connection in the collection will trigger the corresponding change-eventprocedure in the classmodule. -

2.3.5 the use of an Array

Declare a public 1 dimensional array
Public sr
In the event procedure Userfom_Initialize:
Private Sub Userform_Initialize()
ReDim sr(Controls.Count)

For j = 0 to Controls.Count - 1
Set sr(j)= New inputcheck

If typename(controls(j))= "TextBox" Then Set sr(j).cl_textbox=Controls(j)
Next
End Sub
Adjust the array's size to the amount of controls in the userform
ReDim sr(Controls.Count)
Elements in an array can only be approached using their index; the reason why I use a For j ..Next loop.(a lot of alternative methods can be use too )
For j = 0 to Controls.Count - 1
Link every control in the userform to the classmodule by adding a new instance of the classmodule to the array 'sr'.
Since the classmodule is an object we need the instruction 'Set' to add a new instance of the classmodule to the array.
Set sr(j)= New inputcheck
Check what kind of userform control it is to ensure the connection to the corresponding WithEvents-variable in the classmodule.
Connect the control via the link in the array to the corresponding 'WithEvents'-variable in the classmodule.
The event variable in the classmodule is cl_textbox.
Since cl_textbox is an object variable we need the instruction 'Set' to connect the Textbox in the userform to the classmodule objectvariable cl_textbox.
Set sr(j).cl_textbox=Controls(j)
If the user changes any textbox in the userform the connection in the array will trigger the corresponding change-eventprocedure in the classmodule.

Constraints

Not every userformcontrol event can be intercepted by a WithEvents-variable.
These events can't:

- Activate
- Deactivate
- QueryClose
- Resize
- Terminate
- AfterUpdate
- BeforeUpdate
- Enter
- Exit

3 The contents of the example file

- the classmodule 'inputcheck'
- the userform 'form collection', using the 'collection' method.
- the userform 'form array', using the 'array' method.
Het bestand illustreert tegelijkertijd dat de code in een klassemodule voor diverse userforms tegelijkertijd dienst kan doen.

The classmodule 'inputcheck'

The classmodule contains WithEvents variables for:
- Label
- TextBox
- ComboBox
- ListBox
- CheckBox
- OptionButton
- CommandButton
- SpinButton
- MultiPage
- Frame
- UserForm

The classmodule contains all events for every controltype that can be intercepted by a classmodule.
There's code for every controltype in the eventprocedure 'Click'.
The Textbox_change event calls the macro 'A_vervolg'.
That macro checks whether all textboxes in the userform contain any text; and makes the commandbutton 'btn_Next' visible if that is the case.
All other events contain code to show a messagebox; this code has been inoculated by a preceding hyphen.
You can check the functioning of every event if you remove that preceding hyphen.

As an illustration you'll find several kinds of code in the 'click' event of labels in a userform:
- to adapt the content of the control itself
- to adapt the formatting of the control itself
- to adapt another control
- to call a macro
- to call a function
- to adapt a worksheet

Userform 'form_collection' and 'form_array'

The sheet contains a commandbutton to show this userform.
Each userform contains 10 items of every type of controls, the ListBox, Multipage and the Frame excepted.
Both userforms are identical; the only difference resides in the VBA code: the use of the method 'collection' vs. the method 'array'
Both userforms are functionally identical because they both use the same classmodule.

4 Connected subject

Check userinput in ActiveX controls

5 Example files for each controltype

Labels
Textboxes
Comboboxes
Listboxes
Checkboxes
Optionbuttons
Commandbuttons
Spinbuttons
Frames
Multipage
ToggleButton
Scrollbar