Suggestions example file
Check userinput in ActiveX controls in a Worksheet


1 Introduction

2.1 Method I
2.2 Method II
2.3 Method III
2.3.1 Classmodule
2.3.2 Code in the classmodule
2.3.3 Code in the worksheet

3 Technical remarks

4 Example files

5 Similar subject

1 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.

2.1 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.

2.2 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.

2.3 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.

2.3.1 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.

2.3.2 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.

2.3.3 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.

3 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.

4 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

5 Similar subject

Check userform controls using a classmodule