JKP Application Development Services.

                    Microsoft Office Application Development

Handling Worksheet control events using a class module.

Introduction 1 2 3 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:

Option Explicit

Private WithEvents mobtOption As MSForms.OptionButton

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:

Private Sub mobtOption_Click()

End Sub

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:

Option Explicit

Private WithEvents mobtOption As MSForms.OptionButton

Private Sub mobtOption_Change()

End Sub

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:

Private Sub mobtOption_Change()
    If mobtOption.Value = 0 Then
        mobtOption.Object.BackColor = RGB(0, 255, 0)
    Else
        MsgBox "You have selected " & mobtOption.Caption & " from " & mobtOption.GroupName
        mobtOption.Object.BackColor = RGB(255, 0, 0)
    End If
End Sub

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.

<<Previous        Next>>

    Subscribe in a reader

Use the contact page to issue questions or comments about this website.
Copyright © 2003-2007 JKP Application Development Services.