Handling Worksheet control events using a class module.

Pages in this article

1. Preparations
2. Class Module
3. Hooking up
4. Conclusion

Creating the Class module

First, insert a class module.

Figure 2: Menu to insert a class module

In the properties window, change its name from the default Class1 to clsObtHandler:

Figure 3: The properties window showing the name of the classmodule

Now in the codepane, type these lines:

After doing this, you will be able to select mobtOption from the lefthand dropdown list at the top of the codepane:

Figure 4: Dropdown showing new Item for Class module

If you select this, the default Click event skeleton code is inserted in your module:

If now you click on the right dropdown list, you will get all events available for this type of control through the class module:

Figure 5: Events tied to the option button control when invoked through a class module

Note, that the number of available events is less than when using the codemodule behind the worksheet:

Figure 6: events for control as available through the worksheet codemodule

Specifically, for this control the LostFocus event isn't available in a class module. other controls may have more events missing.

Select the Change event from the dropdown and remove the click event. You should now have something like this:

I said I wanted to change the color of the selected control and of course also change the de-selected one back. I also wanted a message about which option button has been selected. I devised this code for that:

When one clicks one option button to select it, both the selected option button and the de-selected option button will fire their change events, so this Event sub will be run twice, once for the selected control and once for the de-selected control. The first will have a Value of 1 and the second a Value of 0.

Frequently asked Questions