Microsoft Office Tutorials and References
In Depth Information
The rest of the code goes into the ThisWorkbook module. It instantiates the grpCBX object and is
refreshed each time the workbook opens by utilizing the Workbook_Open event.
Public myControls As Collection
Private Sub Workbook_Open()
Dim oleCtl As OLEObject, ctl As Class2
Set myControls = New Collection
For Each oleCtl In Worksheets(“Sheet1”).OLEObjects
If TypeOf oleCtl.Object Is MSForms.CheckBox Then
Set ctl = New Class1
Set ctl.grpCBX = oleCtl.Object
In this lesson you create a class module to handle the Click event of some of the OptionButtons on
a UserForm, purposely not involving all OptionButtons in the class.
For this lesson, you design a simple UserForm with eight OptionButtons, of which only five will be
a part of a class module that identifies which OptionButton by name and caption was clicked. To
get the sample database files, you can download Lesson 21 from the book’s website at http://www
Open a new workbook.
Press Alt+F11 to get into the Visual Basic Editor.
From the menu bar, click Insert ➪ UserForm, and size the UserForm to a Height of 200 and a
Width of 400 .
Draw a Label control near the top-left corner of your UserForm, and caption it as
OptionButtons In Class Module .
Draw a Label control near the top-right corner of your UserForm and caption it as Other
OptionButtons . Figure 21-8 shows how your UserForm should look so far.