Check userinput in ActiveX controls in a Worksheet example file

Introduction

You can use ActiveX controls to guide users when they are entering data.
You need a lot of code to prevent any imaginable mistake a user can make.
A classmodule can reduce the amount of code.
The same applies to Userforms. For a description how to do this in a userform see this page.

We'll discuss this method for textboxes in a worksheet.
Userinput results in a change in the text property of a Textbox.
To check what the user does, you can use the textbox's 'Change' event.
Every change in the textbox triggers this event.
Let's take a worksheet, containing 10 textboxes.
The names of these textboxes are: text1, text2.. ...text10.

Method I

In the worksheet's VBA-module you can put this code to check whether all 10 textboxes contain any text.
If they do the button 'Continue' will turn visible.

Private Sub text1_Change()
For j=1 to 10
If Trim(OLEObjects.("Text" & j).Object.Text)="" Then Exit For
Next
OLEObjects("btn_Continue").Object.Visible=j=11
End Sub

Private Sub text2_Change()
For j=1 to 10
If Trim(OLEObjects("Text" & j).Object.Text)="" Then Exit For
Next
OLEObjects("btn_Continue").Object.Visible=j=11
End Sub

Private Sub text3_Change()
For j=1 to 10
If Trim(OLEObjects("Text" & j).Object.Text)="" Then Exit For
Next
OLEObjects("btn_continue").Object.visible=j=11
End Sub

etc.

Method II

Some more efficient code would be:

Private Sub text1_Change()
A_check
End Sub

Private Sub text2_Change()
A_check
End Sub

Private Sub text3_Change()
A_check
End Sub

Private sub A_check()
For j=1 to 10
If Trim(OLEObjects("Text" & j).Object.Text)="" then Exit For
Next
OLEObjects("btn_continue").Object.Visible=j=11
End Sub
But still you need VBA code in every 'change' event of every Textbox.

Method III

An alternative is the use of a classmodule:
- put all textboxes in a 'Collection'
- link the checking code (like A_check) to the -Change-event of this 'collection'

Such a link can only be made using a 'WithEvents' method for a variable.
That use is restricted to a classmodule.
So we have to use one in this case.

Classmodule

How does a classmodule work in this case ?
You link using VBA all textboxes in the worksheet via a 'Collection' to an eventprocedure in the classmodule.
The classmodule contains the code for the eventvariabele.

NB. A classmodule can have several purposes, but we restrict ourselves here to the 'intercepting' of ActiveX-contol events.

What code does the classmodule contain ?

First we give the classmodule a name. Any valid name will do, provided it doesn't interfere with any reserved name in VBA. Including an underscore is a safe method. In this case we use cl_activeXcheck. 1. A public object variable that contains the properties of a certain ActiveX control (e.g. a textbox). Because we want to intercept the event of the textboxes we need to add the instruction 'WithEvents'.

Public WithEvents cl_textbox As MSForms.TextBox
2. One procedure for each 'event' of the object variable cl_textbox (evidently only Textbox events).
In the example file you'll see the events _Change en _DblClick:
Private Sub cl_textbox_Change()
-------
End Sub

Private Sub cl_textbox_DblClick()
-----
End Sub
NB. This single _Change procedure in the classmodule replaces all _change procedures we had before in the worksheet's codemodule.

What's in the worksheet's codemodule ?

Declare a public new 'collection' variable

Public txt_collection as New Collection
In the Workbook_Open event:

Private Sub Workbook_Open()
For Each ctl in OLEObjects
If TypeName(ctl.Object)="TextBox" then
txt_collection.Add New cl_ActiveXcheck,ctl.name
set txt_collection(ctl.name).cl_textbox=ctl.Object
End If
Next
End Sub

For each Textbox you add a new instance of the classmodule to the collection. So every textbox has it's own 'link' to the classmodule stored in the collection.

txt_collection.add New cl_ActiveXcheck, ctl.name

Link every textbox by it's link in the collection to the 'event' variable cl_textbox in the classmodule.
We need the instruction 'Set' because the ActiveX control is an object.

set txt_collection(ctl.name).cl_textbox=ctl.Object
The result is that every change in the textbox will trigger the correponding event procedure in the classmodule: e.g. cl_textbox_change.

Technical remarks

- Since the variable 'txt_collection' is Public you can 'read' this variable in the classmodule.
- Since the variable cl_textbox in the classmodule is a Public variable, you can link all textboxes directly to this variabele ( no need for Property Let).
- Since we added the name of the textbox to the collection as a key, we can refer to every textbox in the collection by it's name. - All example files have been made in Excel 2000.

Example files

The example file containing all previous code

example file

For each ActiveX control a separate example file:

TextBox
ComboBox
CheckBox
OptionButton
ToggleButton
SpinButton
ScrollBar

Similar subject

Check userform controls using a classmodule